Select count group by combined with any-CodePudding

There is this query:

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

which returns

Town         Count
Copenhagen   5
NewYork      6
Athens       7

But I would like an additional line which shows all towns number:

Town         Count
Copenhagen   5
NewYork      6
Athens       7
All          18

CodePudding user response：

Probably not the best way, but I believe this should work:

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', SUM(cnt) AS cnt
FROM counts_by_town
GROUP BY 1;

CodePudding user response：

This question was asked and answered already by Vishal Suthar: SUM of grouped COUNT in SQL Query