Counting and grouping

From listing to adding up

Lesson 3 asked for rows: which towns, which meetings. Plenty of questions want a number instead. How many towns? How many people in the county? What's a typical council meeting's attendance? An aggregate function takes many rows and gives back one answer:

SELECT count(*) FROM towns;

 count 
-------
    12
(1 row)

Think of a hall full of people. Lesson 3 was asking everyone over 40 to stand up, so you could see who they are. This lesson is asking the room to count off, or calling out "everyone from Fairview, over here", and then counting each corner. You stop seeing the people, and start seeing the numbers.

count, and what it counts

count(*) counts rows. count(column) counts rows where that column isn't NULL. Same table, four counts:

SELECT count(*), count(population), count(website), count(phone) FROM towns;

 count | count | count | count 
-------+-------+-------+-------
    12 |    11 |    10 |    11
(1 row)

Twelve towns, 11 with a population (not Dunmore), 10 with a website (not the Ashford village or Juniper), and 11 with a phone (not Juniper). That makes count(column) a quick coverage check: what share of the data is filled in. It's how CivicGrid tracks how many cities have a mayor, or a phone number.

count(DISTINCT column) counts different values. How many towns held at least one meeting?

SELECT count(DISTINCT town_id) AS towns_with_meetings FROM meetings;

 towns_with_meetings 
---------------------
                  11
(1 row)

Eleven of twelve. One town held no meetings at all. Which one is a question this table can't answer by itself, because a town with no meetings has no rows in meetings to find. Lesson 5 joins the tables and finds it.

sum, avg, min and max

SELECT sum(population), min(population), max(population) FROM towns;

  sum   | min  |  max  
--------+------+-------
 172253 | 1288 | 48210
(1 row)

min and max work on dates and text too:

SELECT min(held_on) AS first, max(held_on) AS last FROM meetings;

   first    |    last    
------------+------------
 2026-01-06 | 2026-03-23
(1 row)

Now the average:

SELECT avg(population) FROM towns;

        avg         
--------------------
 15659.363636363636
(1 row)

Long, and worth checking. 172,253 people across 12 towns is 14,354 each. The answer is 15,659, because aggregates skip NULL: avg added up 11 populations and divided by 11, leaving Dunmore out of both. That's usually what you want, since an unknown population shouldn't count as zero. But it means the average is an average of the towns you know about, and the answer won't tell you so. Look at count(column) next to any avg.

round() tidies it. On its own it rounds to a whole number; a second number says how many decimal places to keep:

SELECT round(avg(population)) AS avg_pop, round(avg(population), 1) AS avg_pop_1dp FROM towns;

 avg_pop | avg_pop_1dp 
---------+-------------
   15659 |     15659.4
(1 row)

Several aggregates in one query, each counting what it can:

SELECT count(*) AS meetings, count(attendance) AS with_attendance,
       round(avg(attendance), 1) AS avg_attendance
FROM meetings;

 meetings | with_attendance | avg_attendance 
----------+-----------------+----------------
       30 |              29 |           22.0
(1 row)

Thirty meetings, 29 with an attendance (one Cedar Hollow meeting has none), and an average of 22 people, out of those 29.

GROUP BY: one answer per group

GROUP BY splits the rows into groups that share a value, and gives one answer for each group. How many towns of each kind?

SELECT kind, count(*) FROM towns GROUP BY kind;

  kind   | count 
---------+-------
 city    |     4
 town    |     5
 village |     3
(3 rows)

Several aggregates per group, sorted by one of them:

SELECT kind, count(*) AS towns, sum(population) AS people, round(avg(population)) AS avg_pop
FROM towns GROUP BY kind ORDER BY people DESC;

  kind   | towns | people | avg_pop 
---------+-------+--------+---------
 city    |     4 | 130306 |   32577
 town    |     5 |  36045 |    9011
 village |     3 |   5902 |    1967
(3 rows)

The four cities hold three-quarters of the county. And the towns' average is over 4 of their 5, since Dunmore's population is unknown.

Meetings per town:

SELECT town_id, count(*) AS meetings FROM meetings
GROUP BY town_id ORDER BY meetings DESC, town_id;

 town_id | meetings 
---------+----------
       1 |        4
       3 |        4
       7 |        4
      11 |        4
       5 |        3
      12 |        3
       2 |        2
       4 |        2
       8 |        2
       6 |        1
       9 |        1
(11 rows)

Eleven rows. Town 10, Juniper, isn't here with a zero: it isn't here at all. GROUP BY only makes groups out of rows that exist, and Juniper has none. It's the same gap as count(DISTINCT town_id), and Lesson 5 closes it.

Group by two columns for one row per combination:

SELECT town_id, body, count(*) FROM meetings
GROUP BY town_id, body ORDER BY town_id, body;

 town_id |   body   | count 
---------+----------+-------
       1 | council  |     3
       1 | planning |     1
       2 | council  |     2
       3 | council  |     3
       3 | planning |     1
       4 | council  |     2
       5 | council  |     2
       5 | planning |     1
       6 | council  |     1
       7 | council  |     3
       7 | planning |     1
       8 | council  |     2
       9 | council  |     1
      11 | council  |     3
      11 | planning |     1
      12 | council  |     2
      12 | planning |     1
(17 rows)

Count part of each group with FILTER, a Postgres extra. Meetings per town, and how many have no minutes:

SELECT town_id, count(*) AS meetings,
       count(*) FILTER (WHERE minutes_url IS NULL) AS no_minutes
FROM meetings GROUP BY town_id ORDER BY town_id;

 town_id | meetings | no_minutes 
---------+----------+------------
       1 |        4 |          1
       2 |        2 |          2
       3 |        4 |          0
       4 |        2 |          2
       5 |        3 |          1
       6 |        1 |          0
       7 |        4 |          0
       8 |        2 |          0
       9 |        1 |          1
      11 |        4 |          0
      12 |        3 |          1
(11 rows)

Towns 2 and 4, the Ashford village and Cedar Hollow, have published minutes for none of their meetings. That's the kind of thing a coverage report is for.

string_agg joins a group's text into one line, so you can see who's in each group:

SELECT kind, string_agg(name, ', ' ORDER BY name) AS towns
FROM towns GROUP BY kind ORDER BY kind;

  kind   |                      towns                      
---------+-------------------------------------------------
 city    | Ashford, Brennan Falls, Fairview, Kestrel Bay
 town    | Cedar Hollow, Dunmore, Glen Oak, Harlow, Linden
 village | Ashford, Elk Ridge, Juniper
(3 rows)

The error everyone hits

Ask for each kind, its count, and a town name:

SELECT kind, name, count(*) FROM towns GROUP BY kind;

ERROR:  column "towns.name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT kind, name, count(*) FROM towns GROUP BY kind;
                     ^

The answer has one row per kind, but each kind has several names. Which one should the database show next to "city"? It won't guess. Every column in a grouped SELECT must be either in GROUP BY, or inside an aggregate. Want all the names? string_agg(name, ', '). One of them? min(name). A row per town? Then group by name too, and the counts become per town.

HAVING: filter the groups

Towns with four or more meetings:

SELECT town_id, count(*) FROM meetings
GROUP BY town_id HAVING count(*) >= 4 ORDER BY town_id;

 town_id | count 
---------+-------
       1 |     4
       3 |     4
       7 |     4
      11 |     4
(4 rows)

Why not WHERE?

SELECT town_id, count(*) FROM meetings WHERE count(*) >= 4 GROUP BY town_id;

ERROR:  aggregate functions are not allowed in WHERE
LINE 1: SELECT town_id, count(*) FROM meetings WHERE count(*) >= 4 G...
                                                     ^

It's the order of work from Lesson 3, with one step added:

1. FROM: find the table.
2. WHERE: keep the rows that match.
3. GROUP BY: put the rows into groups.
4. HAVING: keep the groups that match.
5. SELECT, then ORDER BY and LIMIT.

When WHERE runs, there are no groups yet, so there's nothing to count. WHERE filters rows, before grouping. HAVING filters groups, after. You can use both: council meetings only, then towns with three or more of them.

SELECT town_id, count(*) FROM meetings
WHERE body = 'council'
GROUP BY town_id HAVING count(*) >= 3 ORDER BY town_id;

 town_id | count 
---------+-------
       1 |     3
       3 |     3
       7 |     3
      11 |     3
(4 rows)

Finding duplicates

This is the most useful pattern in the lesson: group by the thing that should be unique, and keep the groups with more than one row.

Town names:

SELECT name, count(*) FROM towns GROUP BY name HAVING count(*) > 1;

  name   | count 
---------+-------
 Ashford |     2
(1 row)

Leaders' names, with the towns they're in:

SELECT name, count(*), string_agg(town_id::text, ', ') AS towns
FROM leaders GROUP BY name HAVING count(*) > 1;

     name     | count | towns 
--------------+-------+-------
 Thomas Reyes |     2 | 1, 2
(1 row)

(town_id::text turns the number into text, since string_agg joins text.) Both of these may be fine: names aren't meant to be unique. Now some things that are.

One current mayor per town:

SELECT town_id, count(*) AS current_mayors FROM leaders
WHERE role = 'mayor' AND ended IS NULL
GROUP BY town_id HAVING count(*) > 1;

 town_id | current_mayors 
---------+----------------
       5 |              2
(1 row)

Dunmore, found by a query instead of by reading eleven rows. Lesson 3 spotted it by eye; this would spot it among three thousand towns.

No two town halls share a phone:

SELECT phone, count(*), string_agg(name, ', ') AS towns
FROM towns GROUP BY phone HAVING count(*) > 1;

     phone      | count |         towns         
----------------+-------+-----------------------
 (555) 010-4400 |     2 | Cedar Hollow, Dunmore
(1 row)

That's the same idea as one of CivicGrid's four checks: run after every bulk change, and the right answer is no rows.

No two towns share a website:

SELECT website, count(*), string_agg(name, ', ') AS towns
FROM towns GROUP BY website HAVING count(*) > 1;

 website | count |      towns       
---------+-------+------------------
         |     2 | Ashford, Juniper
(1 row)

A "duplicate" with nothing in it. The Ashford village and Juniper don't share a website; they both have none. WHERE treats NULLs as never equal to anything, but GROUP BY puts all the NULLs in one group. So a duplicate check reports every missing value as one big shared value. (The phone check didn't show this only because Juniper is the one town with no phone: a group of one.) Leave the empties out, and check them separately:

SELECT website, count(*), string_agg(name, ', ') AS towns
FROM towns WHERE website IS NOT NULL
GROUP BY website HAVING count(*) > 1;

 website | count | towns 
---------+-------+-------
(0 rows)

No rows: no two towns share a real website. And the missing ones are a different problem, found with IS NULL in Lesson 3.

A percentage, and integer division again

What share of meetings have minutes?

SELECT count(minutes_url) * 100 / count(*) AS pct_int,
       round(count(minutes_url) * 100.0 / count(*), 1) AS pct
FROM meetings;

 pct_int | pct  
---------+------
      73 | 73.3
(1 row)

22 of 30. Counts are whole numbers, so dividing them is integer division, and the fraction is thrown away: 73. Multiply by 100.0 first, and you get 73.3. Put the 100.0 before the division: count(x) / count(*) * 100.0 would divide first, get 0, and then multiply zero.

Spot the problem

Work out what's wrong before you read the explanation.

The average that went up. A weekly report shows average town population. One week, it jumps from 14,354 to 15,659, and nobody added any people. What happened? Someone noticed that Dunmore's population of 0 was wrong, and set it to NULL, meaning unknown. Before, avg counted Dunmore as zero and divided by 12. Now it skips Dunmore and divides by 11. The fix was right; the report just didn't say how many towns it averaged. Show count(column) next to every average, so a change in what's counted is visible.

Where you are now

You can turn many rows into one answer, or one answer per group, filter the groups, and find duplicates without mistaking empty values for one. But every answer about meetings and leaders has said town_id 5, not Dunmore, and Juniper's zero meetings never showed up at all. Lesson 5 fixes both: joining tables.

Check yourself

Key terms

• Aggregate function: turns many rows into one value, like count, sum, avg, min and max. All but count(*) skip NULL.
• GROUP BY: splits rows into groups that share a value, for one answer per group.
• HAVING: filters groups, after grouping. WHERE filters rows, before.
• FILTER: counts or adds up only part of each group.
• string_agg: joins a group's text into one value.
• Coverage: the share of rows with a value filled in, from count(column) over count(*).
• Duplicate check: GROUP BY the thing that should be unique, HAVING count(*) > 1, with NULLs left out first.

Go deeper

• The PostgreSQL tutorial chapter Aggregate Functions (opens in a new tab).
• Try: the number of leaders per role. (You should get two roles.)
• Try: the busiest planning meeting in the county. (max(attendance) with a WHERE. You should get 15.)
• Try: a coverage line for towns: how many have a phone, as a percentage, to one decimal place. (91.7)