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 
开发者_C百科           FROM Table1 
       GROUP BY colB) AS Result;

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.

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;

The WITH ROLLUP clause might be useful to you here.

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