MySQL SUM aggregate involving a sub-statement that pre selects the rows that are to be SUMmed
Posted on 2007-11-19
I have a MySQL (5) situation where i need to nest a selection "filtering" first and then perform a sum of products (hours * rate) for those rows selected(filtered).
The problem i'm having if i don't "insulate" the selection, the implicit JOIN causes the SUM(hours*rate) to count the rows many times over.
What is the technique, within one overall query, to accomplish the following nesting:
SELECT SUM(hours*rate) FROM
SELECT TableID FROM tbl1, tbl2 WHERE various conditions