PostgreSQL - Calculate SUM() of COUNT()-CodePudding

Basically I have a table called cities which looks like this:

 ------ ----------- --------- ---------- ---------------- 
| id   | name      | lat     | lng      | submitted_by   |
|------ ----------- --------- ---------- ----------------|
| 1    | Pyongyang | 39.0392 | 125.7625 | 15             |
| 2    | Oslo      | 59.9139 | 10.7522  | 8              |
| 3    | Hebron    | 31.5326 | 35.0998  | 8              |
| 4    | Hebron    | 31.5326 | 35.0998  | 10             |
| 5    | Paris     | 48.8566 | 2.3522   | 12             |
| 6    | Hebron    | 31.5326 | 35.0998  | 7              |
 ------ ----------- --------- ---------- ----------------

Desired result:

 ----------- --------- 
| name      | count   |
|----------- ---------|
| Hebron    | 3       |
| Pyongyang | 1       |
| Oslo      | 1       |
| Paris     | 1       |
| Total     | 6       | <-- The tricky part
 ----------- ---------

In other words, what I need to do is SELECT the SUM of the COUNT in the query I'm currently using: SELECT name, count(name)::int FROM cities GROUP BY name;

But apparently nested aggregated functions are not allowed in PostgreSQL. I'm guessing I need to use ROLLUP in some way but I can't seem to get it right.

Thanks for the help.

CodePudding user response：

You need to UMION ALL the total sum.

WITH ROLLUP works by summing up the total for every group seperate and can't be used here.

CREATE TABLE cities (
  "id" INTEGER,
  "name" VARCHAR(9),
  "lat" FLOAT,
  "lng" FLOAT,
  "submitted_by" INTEGER
);

INSERT INTO cities
  ("id", "name", "lat", "lng", "submitted_by")
VALUES
  ('1', 'Pyongyang', '39.0392', '125.7625', '15'),
  ('2', 'Oslo', '59.9139', '10.7522', '8'),
  ('3', 'Hebron', '31.5326', '35.0998', '8'),
  ('4', 'Hebron', '31.5326', '35.0998', '10'),
  ('5', 'Paris', '48.8566', '2.3522', '12'),
  ('6', 'Hebron', '31.5326', '35.0998', '7');

SELECT name, count(name)::int FROM cities GROUP BY name
UNION ALL 
SELECT 'Total', COUNT(*) FROM cities

name      | count
:-------- | ----:
Hebron    |     3
Pyongyang |     1
Oslo      |     1
Paris     |     1
Total     |     6

db<>fiddle here