通过与任意组合选择计数组

Question

有这个查询：

select town, count(town) 
from user
group by town

它返回

Town         Count
Copenhagen   5
NewYork      6
Athens       7

但我想要一个额外的行来显示所有城镇的编号：

Town         Count
Copenhagen   5
NewYork      6
Athens       7
All          18

Answer 1

可能不是最好的方法，但我相信这应该有效：

WITH counts_by_town AS (
    SELECT town, COUNT(town) AS cnt
    FROM user
    GROUP BY 1
)

SELECT town, cnt
FROM counts_by_town
UNION ALL
SELECT 'All' AS town, SUM(cnt) AS cnt
FROM counts_by_town;