MySQL计数()、SUM()和GROUP BY

Question

我有一个特别的问题是关于MySQL的。请看下面的查询和结果集：

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
----------------

我希望得到计数的总和，所以在这种情况下，我希望在下一行或新列中看到'1412‘，这并不重要。

有什么建议吗？我尝试对子查询进行处理，但我找不到合适的解决方案。

干杯

Answer 1

在select语句中，这应该可以工作(未测试)。

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

WITH ROLLUP能满足您的需求吗？

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

Answer 3

在末尾的一行：

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

按c.City排序；