• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5027
  • Last Modified:

MySQL: SUM a Subquery

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
MattKenefick
Asked:
MattKenefick
  • 9
  • 8
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you explain what else are you doing the query that you need to add 1 level?
0
 
MattKenefickAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
MattKenefickAuthor Commented:
Because group kills duplicate items. There are like 500 items, but only want none dupes... and Distinct wont do it.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
MattKenefickAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
hold on, I see what you mean...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
MattKenefickAuthor Commented:
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
 
MattKenefickAuthor Commented:
hang on, let me translate that into my tables and stuff..
0
 
MattKenefickAuthor Commented:
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
 
SharathData EngineerCommented:
Is it possible to provide some sample set and your expected result?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
MattKenefickAuthor Commented:
@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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes.

another method to avoid is would be to migrate to ms sql or oracle or ...
don't shoot the messenger, please :)
0
 
MattKenefickAuthor Commented:
MSSQL / Oracle allow you to SUM a Subquery ?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes.
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.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now