asked on

MySQL SUM aggregate involving a sub-statement that pre selects the rows that are to be SUMmed

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
)

thanks

Guy Hengel [angelIII / a3]

what is the link between the 2 tables?
if there is "none", why do you query it "at the same time"?

willsherwood

ASKER

I'm not sure how to express it: the inner nested query returns the (distinct) keys for the table
that the top-level sum of products then uses just those rows (once).

I'm hoping that a nested technique like this will "insulate" from all the extra SUMmation that it was doing as a flat query

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

willsherwood

ASKER

i hear your need, i'll sanitize for public posting-- will take a while

willsherwood

ASKER

i was able to resolve it using a SUB QUERY this isolated the selection of which rows i wanted to perform the operation on, and then i selected those rows with WHERE ID = (sub SELECT ID FROM ) that returned a column of just the proper row IDs)

thanks

will

willsherwood

ASKER

your questions help me research the right answer, thanks!