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


willsherwoodAsked:
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?
0
 
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"?
0
 
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
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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


thanks

will
0
 
willsherwoodAuthor Commented:
your questions help me research the right answer, thanks!
0
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.