CODEWITHSUNDEEP
postgresql
Mohammed Mortaga
Mohammed Mortaga

Reputation: 139

PostgreSQL - Calculate SUM() of COUNT()

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.

Upvotes: 3

Views: 4497

Answers (2)

nnsense
nnsense

Reputation: 1626

While the wanted result seems simple to achieve with UNION ALL as shown in the accepted answer, I think we should take into account any changes to the main query, so that the total would depend on it instead of just counting all cities (which is basically like running two independent queries), like mentioned into the question what we want is the SUM of the COUNT.

This query will take into account the main one into the sum:

WITH city_counts AS (
  SELECT name, COUNT(*)::int AS count
  FROM cities
  GROUP BY name
  HAVING COUNT(*) < 3 <---------- we only want the cities repeated less than 3 times
)
SELECT name, count
FROM city_counts
UNION ALL
SELECT 'Total', SUM(count)
FROM city_counts;

If we run it against the table in question, we'll get

+-----------+---------+
| name      | count   |
|-----------+---------|
| Pyongyang | 1       |
| Oslo      | 1       |
| Paris     | 1       |
| Total     | 3       |
+-----------+---------+

Hebron won't be listed as <3, and the total (3) is the sum of each city COUNT(*) given by the main query, without Hebron.

Upvotes: 2

nbk
nbk

Reputation: 49373

You need to UNION ALL the total sum.

WITH ROLLUP works by summing up the total for every group separate 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

Upvotes: 3

Related Questions