mysql sum question

Question

Is it possible to get the sum of a column and all the column itself using one query? I mean, I need SUM(Result.num) and the individual Result.num as well, but I dont want to use two separate queries for the purpose? the idea result might be the SUM as another column in the result. Result might look like: col1 Result.num1, SUM Result.num2, SUM Result.num3, SUM ....

 SELECT SUM(Result.num) 
   FROM (SELECT COUNT(colA) AS num 
           FROM Table1 
       GROUP BY colB) AS Result;

 SELECT Result.num 
   FROM (SELECT COUNT(colA) AS num 
           FROM Table1 
       GROUP BY colB) AS Result;

Why not use two queries? how would you like your result to be represented? — Jean-Bernard Pellerin
– Jean-Bernard Pellerin, Commented Jun 17, 2011 at 2:38

IAmTimCorey · Accepted Answer · 2011-06-17 02:38:29Z

4

The answer is that you will do two statements but you can return the results in one result set. You can do that with a UNION ALL statement. Just take your two queries and put a UNION ALL statement between them. It will look like this:

 SELECT Result.num 
   FROM (SELECT COUNT(colA) AS num 
           FROM Table1 
       GROUP BY colB) AS Result;
 UNION ALL
 SELECT SUM(Result.num) 
   FROM (SELECT COUNT(colA) AS num 
           FROM Table1 
       GROUP BY colB) AS Result;

I switched the order around so that your SUM value would be at the end but you could put it at the beginning if you would like.

answered Jun 17, 2011 at 2:38

IAmTimCorey

16.8k5 gold badges42 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

WilliamLou Over a year ago

will mysql execute the subquery twice to get the result set?

NullRef Over a year ago

The sub will be executed once for each side of the union so...yes

NullRef · Accepted Answer · 2011-06-17 02:39:01Z

1

Pretty sure you just need to union your queries

 SELECT SUM(Result.num) FROM (SELECT COUNT(colA) AS num FROM Table1 group by colB) AS Result
union all
 SELECT Result.num FROM (SELECT COUNT(colA) AS num FROM Table1 group by colB) AS Result;

answered Jun 17, 2011 at 2:39

NullRef

3,7381 gold badge22 silver badges18 bronze badges

Comments

Joshua Martell · Accepted Answer · 2011-06-17 03:07:14Z

0

The WITH ROLLUP clause might be useful to you here.

http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html

answered Jun 17, 2011 at 3:07

Joshua Martell

7,2122 gold badges33 silver badges37 bronze badges

Collectives™ on Stack Overflow

mysql sum question

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related