Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2007-11-19
6
Medium Priority
?
428 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 143

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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 20313392
can you please show the table structure, and the foreign key definition, eventually some sample data?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

885 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