?
Solved

MySQL: SUM a Subquery

Posted on 2009-04-15
18
Medium Priority
?
4,462 Views
Last Modified: 2012-05-06
I want to sum a subquery. I have a nested version but it won't because the references in the nest are too far away. The first code example is what I'm trying to do, the second is the workaround that doesn't work.

SELECT SUM(
    SELECT COUNT(*)
    FROM myTable
    WHERE `ref` = `u`.`id`
    GROUP BY `myRow`
) AS `mySum`
FROM `users` `u`
 
--------------------------------
 
SELECT (
 
 SELECT SUM( `myCount` ) AS `mySum`
 FROM (
 
  SELECT  COUNT( * ) `myCount`
  FROM myTable
  WHERE `ref` = `u`.`id`
  GROUP BY `myRow`
 
 ) `subtable`
)
FROM `users` `u`
 
// This query says it can't define what `u` is. The other query won't let me SUM this reference... however, if i SUM it up on it's own with a static reference, it works.

Open in new window

0
Comment
Question by:MattKenefick
  • 9
  • 8
18 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24153306
can you explain what else are you doing the query that you need to add 1 level?
0
 
LVL 4

Author Comment

by:MattKenefick
ID: 24153328
in plain speak (not correct mysql syntax)

UPDATE Table1
SET rowA  =  SUM of COUNT(*)  from Table2 WHERE Table2.x = Table1.y  GROUP BY  z
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24153384
I don't see any use for the GROUP By there?
UPDATE Table1
SET rowA = ( SELECT COUNT(*) from Table2 WHERE Table2.x = Table1.y )

Open in new window

0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 4

Author Comment

by:MattKenefick
ID: 24153436
Because group kills duplicate items. There are like 500 items, but only want none dupes... and Distinct wont do it.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24153448
ok, so what about:
http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html#function_count-distinct
UPDATE Table1
SET rowA = ( SELECT COUNT(DISTINCT z) from Table2 WHERE Table2.x = Table1.y )

Open in new window

0
 
LVL 4

Author Comment

by:MattKenefick
ID: 24153463
I can't do that because I have to apply a "Least" function to each count.

So SELECT LEAST( 50, COUNT(DISTINCT z)) ....
Will always equal 50... meanwhile, I want it to apply that to each grouped item.

SELECT LEAST( 50, COUNT(z))
FROM Table2
....

Gives you different results than

SELECT LEAST( 50, COUNT(*))
FROM Table2
GROUP BY z

Which is what I need.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24153507
still no problem:
UPDATE Table1
SET rowA = LEAST(50, ( SELECT COUNT(DISTINCT z) from Table2 WHERE Table2.x = Table1.y ))

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24153510
hold on, I see what you mean...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24153524
ok, give this a try:
UPDATE Table1 t1
SET rowA = ( SELECT SUM( ( SELECT LEAST(50,COUNT(*)) FROM table2 t1 WHERE t2.x = t.y GROUP BY z ))
               FROM Table1 t  
              WHERE t.key = t1.key
              LIMIT 1
            )

Open in new window

0
 
LVL 4

Author Comment

by:MattKenefick
ID: 24153528
The LEAST needs to be applied to each row of the group by... So even though my LEAST is setup as 50... if I have 4 rows in the GROUP BY, my result could effectively be 200.

Then THAT has to be SUM'd up.

Then THAT has to be SET using an update query.

The only problem I have in my query is that my sub-sub-query doesn't pick up the `user` table reference.


All the queries work individually or broken down, but when i sub-sub-query it... it breaks. If I could simply put a SELECT statement into a SUM() function, it would work... but I can't.
0
 
LVL 4

Author Comment

by:MattKenefick
ID: 24153530
hang on, let me translate that into my tables and stuff..
0
 
LVL 4

Author Comment

by:MattKenefick
ID: 24153555
That query doesn't work cause it's the first problem I mentioned. You can't put a subquery in the SUM function. That's why I'm having such a hard time.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 24154986
Is it possible to provide some sample set and your expected result?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24155748
then you have to use a temp table
CREATE TABLE tmp
SELECT LEAST(50,COUNT(*)) c FROM table2 t1 GROUP BY x, z
;
 
UPDATE Table1 t1
SET rowA = ( SELECT SUM( c )
               FROM tmp   
              WHERE tmp.x = t1.y
            )
;
 
DROP TABLE tmp;

Open in new window

0
 
LVL 4

Author Comment

by:MattKenefick
ID: 24158040
@angelIII

I thought about that, but I was hoping to avoid it.

So if my Table1 had 100,000 rows... the initial query would just create a tmp table that got the counts for all 100,000 rows... then the second would update between the two tables... correct ?

0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 24158076
yes.

another method to avoid is would be to migrate to ms sql or oracle or ...
don't shoot the messenger, please :)
0
 
LVL 4

Author Comment

by:MattKenefick
ID: 24159957
MSSQL / Oracle allow you to SUM a Subquery ?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24160240
yes.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
What we learned in Webroot's webinar on multi-vector protection.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

807 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