SQL Aggregates, GROUP BY and HAVING

Everything so far has returned one output row per table row. Aggregates break that rule: they read a pile of rows and return a single value about the pile. "How many tools do we own?" "What is the average deposit?" "Which shelf holds the most?" None of those can be answered by looking at one row, and none of them can be answered by reading the table by eye once it has more than a screenful in it.

Aggregates are also where SQL stops feeling like a filing system and starts feeling like a reporting tool. As always, each example builds its own table first, because every run in this playground starts from an empty database.

The five aggregate functions you will use daily

COUNT, SUM, AVG, MIN and MAX each take a column (or *, for COUNT) and return one value for the whole result.

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT, deposit INTEGER);
INSERT INTO tools (name, shelf, deposit) VALUES
  ('hedge trimmer', 'garden', 950),
  ('cordless drill', 'power', 1200),
  ('wheelbarrow', 'garden', 500),
  ('tile cutter', 'power', 1500),
  ('hand saw', 'hand', 350);

SELECT
  count(*)      AS tools,
  sum(deposit)  AS total_deposit,
  avg(deposit)  AS mean_deposit,
  min(deposit)  AS cheapest,
  max(deposit)  AS dearest
FROM tools;

tools | total_deposit | mean_deposit | cheapest | dearest
------+---------------+--------------+----------+--------
    5 |          4500 |          900 |      350 |    1500
(1 row)

One row out of five rows in. Note that count(*) counted rows, not values — it never looks inside them — and that avg divides by however many values it saw, so it can hand back a decimal even when every input was a whole number.

Aggregates happily sit next to a WHERE, which runs first and decides which rows the aggregate ever sees:

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT, deposit INTEGER);
INSERT INTO tools (name, shelf, deposit) VALUES
  ('hedge trimmer', 'garden', 950),
  ('cordless drill', 'power', 1200),
  ('wheelbarrow', 'garden', 500),
  ('tile cutter', 'power', 1500),
  ('hand saw', 'hand', 350);

SELECT count(*) AS power_tools, sum(deposit) AS deposit_held
FROM tools
WHERE shelf = 'power';

power_tools | deposit_held
------------+-------------
          2 |         2700
(1 row)

COUNT, NULL, and the empty result

Aggregates other than count(*) skip NULLs entirely. That is usually what you want, and it is always worth knowing about, because it changes what an average is an average of.

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, deposit INTEGER);
INSERT INTO tools (name, deposit) VALUES
  ('hedge trimmer', 950),
  ('donated ladder', NULL),
  ('hand saw', 350);

SELECT
  count(*)        AS rows_in_table,
  count(deposit)  AS deposits_recorded,
  sum(deposit)    AS total,
  avg(deposit)    AS mean
FROM tools;

rows_in_table | deposits_recorded | total | mean
--------------+-------------------+-------+-----
            3 |                 2 |  1300 |  650
(1 row)

count(*) says three rows; count(deposit) says two values. sum added the two it could see, and avg divided by two, not by three — the ladder is not being treated as a zero, it is being left out. If a missing deposit should count as zero in the average, say so: avg(COALESCE(deposit, 0)).

The empty result is the other case to know, and it catches people in real reports:

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, deposit INTEGER);
INSERT INTO tools (name, deposit) VALUES ('hedge trimmer', 950), ('hand saw', 350);

SELECT
  count(*)      AS matching_rows,
  sum(deposit)  AS total,
  total(deposit) AS total_sqlite_style
FROM tools
WHERE deposit > 100000;

matching_rows | total | total_sqlite_style
--------------+-------+-------------------
            0 | NULL  |                  0
(1 row)

No row matched, and the query still returned exactly one row — an aggregate with no GROUP BY always returns one row, even over nothing. count reports 0, but sum of no values is NULL, not zero, which is how a total of NULL ends up on a report that should have shown a money amount. Wrap it: COALESCE(sum(deposit), 0). SQLite also offers total(), which is sum() but returns zero instead of NULL; it is an SQLite extension, so COALESCE is the version to write if the query might ever move to PostgreSQL or MySQL.

COUNT has one more form worth having: count(DISTINCT column) counts different values rather than rows.

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT);
INSERT INTO tools (name, shelf) VALUES
  ('hedge trimmer', 'garden'),
  ('cordless drill', 'power'),
  ('wheelbarrow', 'garden'),
  ('tile cutter', 'power'),
  ('hand saw', 'hand');

SELECT count(*) AS tools, count(DISTINCT shelf) AS shelves_in_use FROM tools;

tools | shelves_in_use
------+---------------
    5 |              3
(1 row)

GROUP BY: one answer per group

GROUP BY sorts the rows into piles by the value of a column, then runs the aggregates once per pile. The result has one row per pile.

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT, deposit INTEGER);
INSERT INTO tools (name, shelf, deposit) VALUES
  ('hedge trimmer', 'garden', 950),
  ('cordless drill', 'power', 1200),
  ('wheelbarrow', 'garden', 500),
  ('tile cutter', 'power', 1500),
  ('hand saw', 'hand', 350),
  ('post driver', 'garden', 1100);

SELECT shelf, count(*) AS tools, sum(deposit) AS deposit_held, max(deposit) AS dearest
FROM tools
GROUP BY shelf
ORDER BY tools DESC, shelf;

shelf  | tools | deposit_held | dearest
-------+-------+--------------+--------
garden |     3 |         2550 |    1100
power  |     2 |         2700 |    1500
hand   |     1 |          350 |     350
(3 rows)

Six rows became three. The rule that keeps GROUP BY queries honest is this: every column in the SELECT list must either be grouped by or wrapped in an aggregate. shelf is grouped by; count, sum and max are aggregates; nothing else is named. Ask for a bare name alongside them and the question is meaningless — there are three names in the garden pile and only one row to put one in.

SQLite will let you ask that meaningless question anyway, and the mistakes section at the end shows what it does. PostgreSQL rejects it outright, which is the friendlier behaviour.

You can group by more than one column, and by an expression:

CREATE TABLE loans (id INTEGER PRIMARY KEY, shelf TEXT, member TEXT, taken_on TEXT);
INSERT INTO loans (shelf, member, taken_on) VALUES
  ('garden', 'Asha',  '2026-03-04'),
  ('garden', 'Bruno', '2026-03-19'),
  ('power',  'Asha',  '2026-03-21'),
  ('power',  'Cleo',  '2026-04-02'),
  ('garden', 'Cleo',  '2026-04-08'),
  ('hand',   'Asha',  '2026-04-27');

SELECT substr(taken_on, 1, 7) AS month, shelf, count(*) AS loans
FROM loans
GROUP BY month, shelf
ORDER BY month, shelf;

month   | shelf  | loans
--------+--------+------
2026-03 | garden |     2
2026-03 | power  |     1
2026-04 | garden |     1
2026-04 | hand   |     1
2026-04 | power  |     1
(5 rows)

Grouping by two things makes a pile per combination, and only the combinations that actually occur appear. Five rows came back out of the six possible month-and-shelf pairs: there is no 2026-03 / hand row, because no hand tool went out in March. GROUP BY never invents an empty group, which matters when you are filling in a report and expect a zero — you have to supply those yourself.

substr(taken_on, 1, 7) takes the first seven characters of a YYYY-MM-DD date, which is its month. SQLite has no date type, so that really is just text slicing; strftime('%Y-%m', taken_on) is the tidier spelling and does the same job here.

HAVING: filtering the groups

WHERE throws away rows before the grouping happens. HAVING throws away whole groups afterwards, which is the only place an aggregate can be tested.

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT, deposit INTEGER);
INSERT INTO tools (name, shelf, deposit) VALUES
  ('hedge trimmer', 'garden', 950),
  ('cordless drill', 'power', 1200),
  ('wheelbarrow', 'garden', 500),
  ('tile cutter', 'power', 1500),
  ('hand saw', 'hand', 350),
  ('post driver', 'garden', 1100);

SELECT shelf, count(*) AS tools, sum(deposit) AS deposit_held
FROM tools
GROUP BY shelf
HAVING count(*) >= 2
ORDER BY shelf;

shelf  | tools | deposit_held
-------+-------+-------------
garden |     3 |         2550
power  |     2 |         2700
(2 rows)

The hand shelf has one tool, so its group failed the HAVING test and disappeared. Two things follow from the order the clauses run in:

• You cannot put an aggregate in WHERE. WHERE count(*) >= 2 is meaningless, because at WHERE time there are no groups yet to count.
• You should not put a plain row condition in HAVING when WHERE could do it. Both may give the same answer, but WHERE throws rows away before the work, and HAVING after it.

Used together they answer a two-part question cleanly — some rows excluded, then some groups excluded:

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT, deposit INTEGER, retired INTEGER);
INSERT INTO tools (name, shelf, deposit, retired) VALUES
  ('hedge trimmer', 'garden', 950, 0),
  ('cordless drill', 'power', 1200, 0),
  ('wheelbarrow', 'garden', 500, 1),
  ('tile cutter', 'power', 1500, 0),
  ('hand saw', 'hand', 350, 0),
  ('post driver', 'garden', 1100, 0);

SELECT shelf, count(*) AS in_service, avg(deposit) AS mean_deposit
FROM tools
WHERE retired = 0
GROUP BY shelf
HAVING avg(deposit) > 800
ORDER BY mean_deposit DESC;

shelf  | in_service | mean_deposit
-------+------------+-------------
power  |          2 |         1350
garden |          2 |         1025
(2 rows)

The retired wheelbarrow never reached the grouping, so the garden average is of the two remaining garden tools rather than three. Then the hand group went, because its average is below 800. SQLite lets HAVING refer to the output alias mean_deposit as well as to avg(deposit); PostgreSQL does not allow the alias there, so writing the function again is the portable choice.

retired is an INTEGER holding 0 or 1 because SQLite has no boolean type. The keywords TRUE and FALSE are accepted and are simply spellings of 1 and 0.

A worked example

One query with all the parts, in the order they run:

CREATE TABLE workshop_sessions (
  id       INTEGER PRIMARY KEY,
  topic    TEXT,
  tutor    TEXT,
  seats    INTEGER,
  booked   INTEGER,
  status   TEXT
);

INSERT INTO workshop_sessions (topic, tutor, seats, booked, status) VALUES
  ('sharpening',  'Asha',  8,  8, 'held'),
  ('sharpening',  'Bruno', 8,  5, 'held'),
  ('bike repair', 'Cleo',  12, 11, 'held'),
  ('bike repair', 'Asha',  12, 12, 'held'),
  ('bike repair', 'Devi',  12, 2, 'cancelled'),
  ('sourdough',   'Devi',  6,  6, 'held'),
  ('mending',     'Bruno', 10, 3, 'held');

SELECT
  topic,
  count(*)                        AS sessions,
  sum(booked)                     AS people,
  round(avg(booked * 1.0 / seats) * 100, 1) AS mean_fill_percent
FROM workshop_sessions
WHERE status = 'held'
GROUP BY topic
HAVING count(*) > 1
ORDER BY mean_fill_percent DESC;

topic       | sessions | people | mean_fill_percent
------------+----------+--------+------------------
bike repair |        2 |     23 |              95.8
sharpening  |        2 |     13 |              81.3
(2 rows)

Reading it in the order the database works:

1. FROM gathers seven rows.
2. WHERE status = 'held' drops the cancelled bike-repair session, leaving six.
3. GROUP BY topic makes four piles: sharpening (2), bike repair (2), sourdough (1), mending (1).
4. The SELECT list runs per pile. booked * 1.0 / seats needs the * 1.0 for the reason from lesson 1 — 11 / 12 in whole numbers is 0.
5. HAVING count(*) > 1 drops sourdough and mending, which had one session each.
6. ORDER BY sorts what is left.

Bike repair comes out ahead on average fill even though the sharpening pair includes a full session, because averaging 11/12 and 12/12 beats averaging 8/8 and 5/8.

Common mistakes

Selecting a column that is neither grouped nor aggregated

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT, deposit INTEGER);
INSERT INTO tools (name, shelf, deposit) VALUES
  ('hedge trimmer', 'garden', 950),
  ('wheelbarrow', 'garden', 500),
  ('post driver', 'garden', 1100),
  ('hand saw', 'hand', 350);

SELECT shelf, name, count(*) AS tools
FROM tools
GROUP BY shelf;

shelf  | name          | tools
-------+---------------+------
garden | hedge trimmer |     3
hand   | hand saw      |     1
(2 rows)

The garden group contains three tools and the result shows one name for it. SQLite picked a row from the group and used its name, without a word of complaint; which row it picks is not something you can rely on, and it can change between versions or when an index appears. PostgreSQL refuses this query with an error naming the offending column, and MySQL refuses it too under its default settings. This is the single most important portability trap in GROUP BY: if your grouped query runs in SQLite but not elsewhere, this is usually why. Either group by the column or wrap it in an aggregate such as min(name) or group_concat(name).

One documented exception: when the SELECT list contains exactly one min() or max(), SQLite takes the bare columns from the row that produced that minimum or maximum. So adding max(deposit) to the query above really would name the dearest tool on each shelf. It is genuinely useful, and it is also an SQLite feature rather than standard SQL — the same query elsewhere needs a window function or a join.

Putting an aggregate in WHERE

CREATE TABLE tools (id INTEGER PRIMARY KEY, shelf TEXT);
INSERT INTO tools (shelf) VALUES ('garden'), ('garden'), ('hand');
SELECT shelf, count(*) FROM tools WHERE count(*) > 1 GROUP BY shelf;

Error in the statement on line 3: misuse of aggregate: count()

"Misuse of aggregate" is SQLite's way of saying that count() has no meaning at WHERE time, because the groups it would count do not exist yet. Move the condition to HAVING, which runs after they do.

Counting a column that contains NULLs and expecting a row count

CREATE TABLE loans (id INTEGER PRIMARY KEY, member TEXT, returned_on TEXT);
INSERT INTO loans (member, returned_on) VALUES
  ('Asha', '2026-03-11'),
  ('Bruno', NULL),
  ('Cleo', NULL),
  ('Asha', '2026-04-02');

SELECT count(*) AS loans, count(returned_on) AS returned, count(*) - count(returned_on) AS still_out
FROM loans;

loans | returned | still_out
------+----------+----------
    4 |        2 |         2
(1 row)

Four loans, two returned. Writing count(returned_on) when you meant "how many loans" quietly halves the answer, and nothing about the output announces that it happened. count(*) when you mean rows, count(column) only when you mean "rows where this is known" — and the subtraction above is a neat way to count the NULLs deliberately.

Assuming group_concat is spelled the same everywhere

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT);
INSERT INTO tools (name, shelf) VALUES
  ('hedge trimmer', 'garden'),
  ('wheelbarrow', 'garden'),
  ('hand saw', 'hand');

SELECT shelf, count(*) AS tools, group_concat(name, ', ') AS contents
FROM tools
GROUP BY shelf
ORDER BY shelf;

shelf  | tools | contents
-------+-------+---------------------------
garden |     2 | hedge trimmer, wheelbarrow
hand   |     1 | hand saw
(2 rows)

Gluing a group's values into one piece of text is genuinely handy, and it is the one common aggregate with a different name in every dialect: group_concat(name, ', ') in SQLite, GROUP_CONCAT(name SEPARATOR ', ') in MySQL, and string_agg(name, ', ') in PostgreSQL. The order of the names inside the text is also not guaranteed unless you ask for one.

Next steps

You can now summarise a table from any angle. The catch is that everything so far has come from a single table, and real data is spread across several — tools in one, members in another, loans linking them. The next lesson is about putting those tables back together, and about why a join sometimes returns more rows than you started with and sometimes fewer. Try the worked example in the SQL playground with the HAVING line deleted to see the groups it removed.