🗄 SQL & Databases · Chapter 2

Aggregation: GROUP BY and HAVING

GROUP BY answers 'how many' and 'how much' — and the only question that matters is: one row per what?

Study this lesson free in the app →

The basics

GROUP BY collapses many rows into one row per group and computes a summary — a count, sum, average, min or max — for each group.

The problem it solves: you rarely want four million individual rides; you want "rides and revenue per city", "average fare per day", "drivers with more than 20 trips". GROUP BY turns raw events into the numbers dashboards and decisions run on.

The mental model: sorting laundry. Split the pile into baskets by a rule (GROUP BY city), apply something to each basket (count(*), sum(fare)), combine one output line per basket. After grouping, individual rides are gone — which is why every selected column must be grouped or aggregated. Before writing any aggregation, finish the sentence "I want one row per ______"; the blank is your GROUP BY, and it is called the grain.

Explained

SELECT city, count(*) AS rides, sum(fare) AS revenue
FROM rides
WHERE status = 'completed'
GROUP BY city;

Four million rows in. Twelve rows out — one per city.

That phrase is the whole lesson. Before writing any aggregation, finish this sentence: "I want one row per ______." Whatever fills the blank goes in the GROUP BY.

Sorting the laundry

A pile of clothes on the floor. You sort it into baskets — shirts, trousers, socks — then count each basket.

Three steps, and SQL does exactly these three:

Split — put every item into a basket by some rule (GROUP BY city). Apply — do something to each basket (count, sum, avg). Combine — one line of output per basket.

The pile is gone afterwards. You have counts, not clothes. That's why you can't ask for an individual ride's id in a query grouped by city — you sorted the rides into baskets and the individual items are no longer available to name.

(When you do need both the item and its basket's total, that's a window function — a later lesson.)

The rule that causes every error

Every column in SELECT must either be in the GROUP BY, or be inside an aggregate function.

-- ✗ ride_id is neither grouped nor aggregated
SELECT city, ride_id, count(*)
FROM rides GROUP BY city;
-- ERROR: column "rides.ride_id" must appear in the GROUP BY clause

-- ✓ grouped
SELECT city, status, count(*) FROM rides GROUP BY city, status;

-- ✓ aggregated
SELECT city, max(ride_id), count(*) FROM rides GROUP BY city;

The error message is one of the most helpful in SQL, and it's asking a real question: there are 4,000 ride_ids in the Shillong basket — which one did you want?

The aggregate functions:

count(*)              -- rows in the group, including NULLs
count(fare)           -- rows where fare IS NOT NULL  ← different!
count(DISTINCT ride_id)
sum(fare)   avg(fare)   min(fare)   max(fare)

count(*) versus count(column) is a genuine trap: the second silently skips NULLs, so the two can differ and neither is wrong — they answer different questions.

Watch six rows become three

WHERE filters rows before grouping. HAVING filters groups after. If you can say which one you need, you understand aggregation.

Three counts, three answers

The rides table has 10 rows. Of those, 2 have a NULL fare, and 3 rows share the same ride_id (a duplicate delivered three times).

SELECT count(*),
       count(fare),
       count(DISTINCT ride_id)
FROM rides;

Grain: decide it before you type

Grain is what one row of your result represents. Getting it wrong is the most expensive mistake in analytics, and it never raises an error.

GROUP BY city                      -- one row per city
GROUP BY city, date                -- one row per city per day
GROUP BY city, date, driver_id     -- one row per city per day per driver

Each line makes the result larger and more detailed. Each also changes what the numbers mean — the same sum(fare) is city revenue, then daily city revenue, then that driver's contribution.

The practical failure: someone builds a daily city revenue table, someone else joins it to another table and sums it, and now revenue is counted once per driver. Nothing errors. The number is simply four times too big.

Two habits that prevent it:

1. Name the table after its grain — daily_revenue_by_city, not revenue
2. Test the grain — the count of rows should equal the count of distinct grouping keys:

SELECT count(*), count(DISTINCT city || date) FROM daily_revenue_by_city;
-- these two numbers MUST match

That's a one-line uniqueness test, and it's the single most valuable check in a data pipeline.

The average that was wrong for a year

A weekly report shows average ride fare: ₹312. Operations plan pricing around it.

The query:

SELECT avg(fare) FROM rides WHERE status = 'completed';

What nobody noticed: about 8% of completed rides are cash rides where fare is recorded as NULL because it's collected offline.

avg skips NULLs, so the reported average is the average of card rides only — and card rides skew higher, because cash is used more in short local trips.

The true average across all completed rides was around ₹287. Every pricing decision for a year was made on a number that was 9% high, and the query was never wrong — it just answered a narrower question than the label on the dashboard claimed.

The fixes, and they're a choice not a formula:

-- treat missing as zero (usually wrong for money)
avg(coalesce(fare, 0))

-- be explicit about what's excluded, and count it
select avg(fare)                    as avg_fare_where_known,
       count(*)                     as rides,
       count(fare)                  as rides_with_fare,
       count(*) - count(fare)       as rides_missing_fare

The second version doesn't just fix the number — it puts the exclusion on the dashboard, where someone will see it.

One row per ______. Fill that blank; it is your GROUP BY.

WHERE filters rows before grouping. HAVING filters groups after.

Aggregates skip NULLs. count(*) ≠ count(col), and avg divides by the non-null count.

Words to know

In two minutes

• GROUP BY splits rows into groups, applies aggregates, and returns one row per group.
• Decide the grain first — 'one row per city per day' — and name tables after it.
• Every selected column must be in GROUP BY or inside an aggregate; the error is asking which of thousands of values you meant.
• WHERE filters rows before grouping; HAVING filters groups after. Row filters belong in WHERE.
• Aggregates skip NULLs: count(col) ≤ count(*), avg divides by the non-null count, sum of all NULLs is NULL.
• Deduplicate before aggregating — use count(DISTINCT id) — because at-least-once delivery inflates counts and sums.
• Check the row count either side of every join; a fan-out join silently multiplies revenue.
• Test grain with count(*) = count(DISTINCT key); distinct counts do not add across days.

Test yourself

There are 9 questions, 3 puzzles and 7 recall cards for this lesson in the app — the recall cards come back on later days, just before you would forget.