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

Martin Smith · Accepted Answer · 2011-09-28 16:06:20Z

7

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;

answered Sep 28, 2011 at 16:06

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

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

Comments

Conrad Frix · Accepted Answer · 2011-09-28 16:06:53Z

5

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;

edited Sep 28, 2011 at 16:06

Conrad Frix

52.8k13 gold badges100 silver badges158 bronze badges

answered Sep 28, 2011 at 16:04

Rene Pot

24.8k7 gold badges70 silver badges92 bronze badges

Comments

DavidEG · Accepted Answer · 2011-09-28 16:08:30Z

0

On one row 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;

answered Sep 28, 2011 at 16:08

DavidEG

5,9673 gold badges33 silver badges46 bronze badges

Comments

John · Accepted Answer · 2011-09-28 16:28:43Z

0

With 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

answered Sep 28, 2011 at 16:28

John

192 bronze badges

Collectives™ on Stack Overflow

MySQL COUNT(), SUM() and GROUP BY

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related