Aggregates: COUNT, GROUP BY and HAVING

So far we've been looking at rows. But half of real questions are not "show me" but "count": how many orders, for what total, what's the average check. That's what aggregate functions do — they collapse many rows into one number.

Five functions

live example

SELECT COUNT(*) FROM orders WHERE status = 'PAID';

COUNT(*) — how many rows. The other four work on a column's values:

• SUM(total_amount) — the total;
• AVG(total_amount) — the average;
• MIN(total_amount) / MAX(total_amount) — minimum and maximum.

Several at once is fine:

live example

SELECT COUNT(*), SUM(total_amount), AVG(total_amount) FROM orders WHERE status = 'PAID';

One nuance that marks a professional: COUNT(*) counts rows, while COUNT(total_amount) counts rows where total_amount is not empty (not NULL). On clean data the numbers match; on dirty data they diverge — and that divergence is a finding in itself. And COUNT(DISTINCT customer_id) counts unique values: not "how many orders" but "how many different customer ordered."

Practice here: Price spread on the storefront

GROUP BY: count per group

One number for the whole table is rarely what you need. More often you need a breakdown: how many orders in each status. That's GROUP BY:

live example

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

status          | count | sum
CANCELLED       | 4     |  8680.00
COMPLETED       | 5     | 33440.00
DELIVERED       | 3     | 21930.00
PAID            | 4     | 23920.00
PENDING_PAYMENT | 2     | 16970.00
…

The database sorted the rows into piles with the same status and counted each pile separately. You can group by several columns: GROUP BY status, customer_id — "per customer per status."

The main GROUP BY rule: SELECT may contain only columns listed in GROUP BY and aggregate functions. SELECT status, total_amount … GROUP BY status won't run — the database reasonably asks: "the 'PAID' pile has two different amounts; which one should I show?"

Practice here: Orders and revenue by seller · Orders broken down by status

HAVING: a filter after counting

WHERE filters rows before grouping and can't use aggregates. But what if the condition is on the count itself ("show customer with more than five orders")? That's HAVING — a filter after counting:

live example

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

Easy to memorize: WHERE is about rows, HAVING is about groups. They coexist happily: WHERE status = 'PAID' first keeps paid orders, GROUP BY customer_id groups them, HAVING SUM(total_amount) > 10000 keeps only the big customer.

Practice here: Customers who ordered more than twice

Where this applies

Aggregates reconcile worlds: the UI says "you have 17 orders" — COUNT(*) against the database must say the same; a report computed revenue — SUM confirms or refutes it. Plus anomaly hunting in one line: GROUP BY email HAVING COUNT(*) > 1 finds duplicate users, MIN(created_at) answers "since what year do we have data," and comparing COUNT(*) with COUNT(email) instantly shows how many records lack an email.

Where beginners stumble:

• They put an aggregate in WHERE (WHERE COUNT(*) > 5) — an error: conditions on counts live in HAVING.
• They mix aggregates with "plain columns" missing from GROUP BY in the SELECT. The database honestly refuses to guess which of the many values to show.
• They count over rows duplicated by a JOIN. First make sure the join didn't multiply rows, then count — otherwise SUM and COUNT lie severalfold.

What to learn next. GROUP BY has a ceiling: it collapses rows, while you often need both the rows and the counts at once — that's what window functions do. And the COUNT(*) vs COUNT(column) difference leads you to NULL and data types.