PostgreSQL - Calculate SUM() of COUNT()

Question

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.

@nbk UNION sounds like a good idea but I can't see how it can be used if I still can't access the COUNT. Here's what I'm getting: HINT: There is a column named "count" in table "*SELECT* 1", but it cannot be referenced from this part of the query. — Mohammed Mortaga
– Mohammed Mortaga, Commented Mar 23, 2022 at 10:10
@a_horse_with_no_name Oh, thanks for pointing out, it's just a typo. Fixed it. — Mohammed Mortaga
– Mohammed Mortaga, Commented Mar 23, 2022 at 10:27

Mohammed Mortaga · Accepted Answer · 2022-03-25 13:59:56Z

2

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

edited Mar 25, 2022 at 13:59

Mohammed Mortaga

1392 silver badges8 bronze badges

answered Mar 23, 2022 at 10:18

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

nnsense Over a year ago

I've tried this but it seems to me that the second SELECT is completely unrelated the first query, for example as soon as you add an "HAVING" to the first query (HAVING COUNT(*) > 1) the total doesn't match (it is still 6, not 3).

nbk Over a year ago

Having males no sense, as the sum I. The second query only returned nacone value the total sum of all values above. Ease make a ne question with table data and wanted result to see what can be done

nnsense Over a year ago

Sorry to insist, but I think it does makes sense (if I'm getting what you wrote, what "nacone" means?), OP said what he wanted was to "SELECT the SUM of the COUNT in the query I'm currently using", this isn't using his query, it is querying the DB regardless the result of that, since that's exactly what I'm trying to achieve I was interested exactly in this feature, but I will create a new question as advised.

nbk Over a year ago

As I said make a new question and add data to it , if you oblx wyntt to count the selected data , make first a CTE that select all data the make the above query with the CTE, that is simple and would give the result, so the answer is correct for this Szenario

nnsense · Accepted Answer · 2023-05-23 21:31:09Z

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.

Collectives™ on Stack Overflow

PostgreSQL - Calculate SUM() of COUNT()

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related