Solved

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

Posted on 2007-11-19
6
421 Views
Last Modified: 2012-06-27
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
Comment
Question by:willsherwood
  • 4
  • 2
6 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20312652
what is the link between the 2 tables?
if there is "none", why do you query it "at the same time"?
0
 

Author Comment

by:willsherwood
ID: 20312798
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
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 20313392
can you please show the table structure, and the foreign key definition, eventually some sample data?
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:willsherwood
ID: 20313408
i hear your need, i'll sanitize for public posting-- will take a while
0
 

Author Comment

by:willsherwood
ID: 20316794
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
 

Author Closing Comment

by:willsherwood
ID: 31409940
your questions help me research the right answer, thanks!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question