Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 429
  • Last Modified:

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


0
willsherwood
Asked:
willsherwood
  • 4
  • 2
1 Solution
 
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you please show the table structure, and the foreign key definition, eventually some sample data?
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now