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


Who is Participating?
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
can you please show the table structure, and the foreign key definition, eventually some sample data?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what is the link between the 2 tables?
if there is "none", why do you query it "at the same time"?
willsherwoodAuthor Commented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

willsherwoodAuthor Commented:
i hear your need, i'll sanitize for public posting-- will take a while
willsherwoodAuthor Commented:
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)


willsherwoodAuthor Commented:
your questions help me research the right answer, thanks!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.