MySQL COUNT(), SUM() and GROUP BY

Question

I have a particular question which is about MySQL. Please have a look at the query and the result set below:

SELECT COUNT(c.Population) AS c, c.City AS cc
FROM City c
GROUP BY c.City
ORDER BY c.City;

261 | Bristol
----------------
910 | London
----------------
241 | Manchester
----------------

I'd like to get the SUM of the COUNTs, so in this case I'd like to see '1412' in the next row, or in a new column, it doesn't really matter.

Any advice? I have tried to apply sub-queries to this but I couldn't find a proper solution.

Cheers

Answer 1

Does WITH ROLLUP do what you need?

SELECT 
      COUNT(c.Population) AS c, 
      c.City AS cc 
FROM City c 
GROUP BY c.City 
WITH ROLLUP;

Answer 2

in the select statement, this should work (untested).

SELECT 
    COUNT(c.Population) AS c, 
    c.City AS cc, 
    (SELECT COUNT(c.Population) FROM City) as TotalPop 
FROM City c 
GROUP BY c.City 
ORDER BY c.City;