Aggregate Functions, GROUP BY and HAVING

The 24-chapter SQL for Beginners series has so far asked about individual rows; chapter 6 asks what whole sets of rows add up to. This chapter introduces the five aggregate functions, the difference between COUNT(*) and COUNT(column), how GROUP BY slices a table into buckets, and how HAVING filters the buckets once they exist.

The five aggregate functions

An aggregate function takes many rows and returns one value. SQL ships five that cover almost every summary you will ever need:

Run them against the whole table and the result is a single row with a single value:

SELECT COUNT(*) AS customers, MIN(signup_date) AS first_signup, MAX(signup_date) AS latest_signup
FROM customers;

Expected output:

6|2025-11-02|2026-02-14

Three aggregates, one row — that is the shape of every summary: many rows in, one row out. ROUND(AVG(price), 2) rounds the mean of the six prices (499, 249, 899, 749, 349 and 649) to 565.67, and because AVG ignores NULLs rather than treating them as zero, a single missing value changes the denominator behind your back.

Aggregates accept expressions, so the shop’s total revenue is one line — every line item’s quantity times its unit price, added up:

SELECT SUM(quantity * unit_price) AS revenue FROM order_items;

Expected output:

6439

6439 rupees across the eight line items, a single number where eight rows used to be. The arithmetic runs row by row first and the sum happens afterwards, which means a NULL unit_price would make that row’s contribution NULL and the total would quietly drop it — not an error, just a smaller number than you expected.

COUNT in three flavours

COUNT is the aggregate you will use daily, and its three forms answer three different questions:

SELECT COUNT(*) AS rows, COUNT(city) AS cities, COUNT(DISTINCT country) AS countries
FROM customers;

Expected output:

6|6|5

• COUNT(*) — counts rows, whatever they contain. This is the “how many” you almost always want.
• COUNT(city) — counts rows where city is not NULL. Equal to COUNT(*) only while every row has a city; the gap between the two numbers is a NULL detector.
• COUNT(DISTINCT country) — counts distinct values: six customers, five countries, because India appears twice.
• Distinct per bucket — COUNT(DISTINCT status) inside a GROUP BY counts different values within each group, which is how you ask how many distinct statuses each customer used.

On an empty result COUNT(*) honestly returns 0 while SUM and AVG return NULL — a distinction worth remembering before you print a total in an application.

GROUP BY: slicing the table

GROUP BY breaks the rows into buckets by the listed columns, then the aggregates run inside each bucket. Orders by status:

SELECT status, COUNT(*) AS orders FROM orders GROUP BY status;

Expected output:

cancelled|1
paid|3
shipped|2

One row per distinct status instead of one row overall: the table was split into three buckets of 1, 3 and 2 rows and COUNT(*) ran once per bucket. The column left of the aggregate is the grouping key, and its value in each output row is the value shared by every row in that bucket.

Two things to notice. SQLite happened to return the buckets alphabetically, but GROUP BY promises no order whatsoever — add ORDER BY whenever the sequence matters. And the result is a new table: you can filter, sort and join it like any other, which is exactly what the next section does.

A filter can also decide which rows reach the grouping in the first place. Only products above 300, by category:

SELECT category, COUNT(*) FROM products WHERE price > 300 GROUP BY category;

Expected output:

Fiction|1
Kids|2
Tech|2

The Very Hungry Caterpillar (249) never entered the bucket, so Kids falls from three products to two, and Fiction, which holds a single title, keeps just that one. WHERE is still evaluated before grouping, so this is the same early filtering chapter 4 taught — it just happens to sit in front of a GROUP BY instead of a SELECT.

Sort the buckets afterwards and a summary becomes a ranked list — customers by country, biggest group first:

SELECT country, COUNT(*) AS customers
FROM customers
GROUP BY country
ORDER BY customers DESC, country;

Expected output:

India|2
Canada|1
Germany|1
Kenya|1
Singapore|1

Five rows, one per country, ties broken alphabetically so the order is reproducible on any engine. Notice that the ordering happens after grouping: ORDER BY sees the finished five-row result, not the six original customers, which is why it may also sort by the customers alias you created for the count.

Several keys, several aggregates

Group by more than one column and each combination becomes its own bucket. A stock report by category in one pass:

SELECT category,
       COUNT(*) AS items,
       SUM(stock) AS units,
       ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category;

Expected output:

Fiction|1|40|499.0
Kids|3|105|415.67
Tech|2|40|824.0

Every aggregate agrees with the sample data: Fiction holds only The Silent Patient, so 40 units at a mean of 499.0, while Kids spreads 105 units across three titles for a mean of 415.67. Multiple aggregates over one grouping are free — the buckets are built once and every function reads them. One rule to carry away: never average an average. The mean of the three category means here comes to about 579.56, which is not the 565.67 the ungrouped AVG(price) gives, because the groups differ in size — compute an overall figure from the raw rows instead.

The grouped result behaves like any other table, so the chapter 5 moves apply unchanged: ORDER BY category or ORDER BY units DESC will arrange the report, and LIMIT will cut it to the top few lines.

HAVING filters groups

WHERE removes rows before grouping; HAVING removes buckets after the aggregates have run. That is why a condition on an aggregate can only live in HAVING — the aggregate does not exist yet when WHERE runs. Customers who placed more than one order:

SELECT customer_id, COUNT(*) AS orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1;

Expected output:

1|2

Only customer 1 ordered twice. Push the condition further and the separation becomes clearer — orders whose line items total at least a thousand:

SELECT order_id, SUM(quantity * unit_price) AS total
FROM order_items
GROUP BY order_id
HAVING SUM(quantity * unit_price) >= 1000;

Expected output:

4|1347
6|1798

Order 1 came to 997 and misses the cut; orders 4 (2 × 499 + 349) and 6 (2 × 899) pass. Both clauses can appear in one query — WHERE first, to decide which rows are grouped at all, then GROUP BY, then HAVING to drop buckets. A typical shape is WHERE order_date >= '2026-02-01' GROUP BY customer_id HAVING COUNT(*) >= 3: recent orders only, then only the customers who placed several of them.

Because HAVING runs after the select list is built, you may repeat the aggregate or use its alias — SQLite, MySQL and PostgreSQL all accept HAVING orders > 1 here, where the same name in WHERE would be rejected. Repeating COUNT(*) is the conservative choice when you plan to move the query to a stricter engine; both forms mean the same thing.

Drop the GROUP BY entirely and HAVING still works: the whole table becomes one bucket, so HAVING COUNT(*) > 100 is a guard that passes only when the table has grown past a hundred rows. Rare, but it explains the syntax you meet in scripts that check whether a table has been loaded.

Bare columns and strict modes

One rule deserves its own warning: any column in the select list that is not aggregated should also appear in GROUP BY. SQLite is forgiving and simply picks an arbitrary row’s value, which is why this query runs — and why its middle column is not to be trusted:

SELECT status, customer_id, COUNT(*) FROM orders GROUP BY status;

Expected output:

cancelled|2|1
paid|1|3
shipped|1|2

Each customer_id is one member of the bucket chosen by chance, so “cancelled” shows customer 2 (correct, it is the only one) while “paid” shows customer 1 rather than the three customers involved. PostgreSQL rejects the query outright, and MySQL does too since 5.7 because ONLY_FULL_GROUP_BY is the default. The portable habit: group by every bare column, or do not select it.

The same discipline catches the two neighbouring mistakes. A column that is grouped but never selected simply does not appear in the output, and one that is selected but never grouped invents a value for you. Neither is a bug in SQLite — both are the rule working as designed, which is why knowing the rule beats memorising which engine complains about what.

Common mistakes

• Putting an aggregate in WHERE — WHERE COUNT(*) > 1 is invalid because WHERE runs before grouping; use HAVING.
• Forgetting GROUP BY entirely — SQLite collapses the table to one row of arbitrary values, while stricter databases raise an error; both mean the buckets were never declared.
• Expecting GROUP BY to sort — bucket order is unspecified on every engine; append ORDER BY if you are showing the result to a human.
• Confusing COUNT(*) with COUNT(col) — the second skips NULLs, so the two diverge as soon as a column is optional.
• Averaging averages — the mean of group means is weighted wrongly; compute the overall figure from the raw rows, not from the summary.
• Selecting a bare column you did not group by — SQLite fills it in with a random member of the bucket and other engines refuse; group by it or drop it.

Practice checklist

1. Count the rows in every table with COUNT(*) and check for 6, 6, 6 and 8.
2. Group products by category and return the cheapest and priciest price per category with MIN and MAX.
3. List every distinct order id from order_items with COUNT(DISTINCT order_id) and check it against the six rows in orders.
4. Write a query with both a WHERE and a HAVING clause — say, orders after 2026-02-01 grouped by status — and count the rows before and after.
5. Run the bare-column example from this chapter twice and watch which customer_id each bucket hands back.

What's next?

Reading and summarising data is covered; now the series writes some. Next you insert, update and delete rows without losing track of what you changed: Inserting, Updating and Deleting Data.