MySQL COUNT (), SUM () and GROUP BY

Question

I have a special question regarding MySQL. Please look at the query and the result:

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 would like to get SUM from COUNT, so in this case I would like to see "1412" in the next row or in a new column, it does not really matter.

Any tips? I tried applying subqueries to this, but I could not find the right solution.

Greetings

Answer 1

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

 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; 

Answer 2

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 3

In one line at the end:

 SELECT * FROM ( SELECT 0 as total , COUNT(c.Population) AS c , c.City AS cc FROM City c GROUP BY c.City union SELECT 1 as total , COUNT(c.Population) AS c , null AS cc FROM City ) myquery ORDER BY total , city 

ORDER BY c.City;

Answer 4

With a simple UNION;

 select count(d.Population) as c, 'TOTAL' as cc from City d union SELECT COUNT(c.Population) AS c, c.City AS cc FROM City c GROUP BY c.City ORDER BY c.City; 

John