CODEWITHSUNDEEP
mysqlsqlaggregate-functions
WilliamLou
WilliamLou

Reputation: 1904

mysql sum 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;

Upvotes: 0

Views: 105

Answers (3)

Joshua Martell
Joshua Martell

Reputation: 7202

The WITH ROLLUP clause might be useful to you here.

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

Upvotes: 0

NullRef
NullRef

Reputation: 3743

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;

Upvotes: 1

IAmTimCorey
IAmTimCorey

Reputation: 16755

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.

Upvotes: 4

Related Questions