Chapter 01 · 9 min · Basic

The aggregate that lies

You have written COUNT, AVG and JOIN already. This chapter is not about how they work. It is about the fact that on real data they each quietly make a decision for you, and nobody tells you which one they made.

Here is the situation you will be in. A manager asks what the average rating is. You run one query, you say a number out loud, and somebody else in the room ran a slightly different query and got a different number. Neither of you wrote a bug. The difference is that NULL means we do not know, and every aggregate in SQL has its own opinion about what to do with not knowing.

By the end of this chapter you will be able to produce four defensible answers to one question, and — the part that matters in the job — say which one you chose and why.

The dataset

Eight rides from a ride-hailing app. Small enough to read end to end — do read it, because every result below is checkable by eye, and the point of this chapter is that you will still get it wrong. Note two things now: rating and fare_myr both have gaps, and rides 7 and 8 belong to riders who are not in the riders table.

Schema

▸riders

rider_id	int
home_city	text

▸rides

ride_id	int
rider_id	int
city	text
fare_myr	numeric(6,2)
rating	int
status	text

▸flagged_riders

rider_id

int

Example data

▸riders

▸rides

▸flagged_riders

Count the rides. Now count them again.

Start with the question that looks like it cannot go wrong: how many rides are there?

There are eight rows. But watch what happens when you count a column instead of counting rows.

COUNT(*) counts rows. COUNT(rating) counts rows where rating is not null — it silently skips the unknowns. That is not a bug or an edge case, it is the defined behaviour of every aggregate in SQL except COUNT(*): NULLs are not counted, not summed, not averaged. They are dropped.

So already you have two honest answers to "how many rides": eight, and four. The second one is really answering a different question — how many rides did somebody rate — and the only thing separating them is which characters you typed inside the brackets.

Before you run it: rows_in_table is 8. What are the other two?

Editable, try changing it

Basic

The rest of this chapter is Basic

Learning SQL is free here, forever. This track is the paid half: what to do when the data is dirty, duplicated and undocumented, and somebody still wants a number.

See Basic plans

RM 25/mo · cancel anytime

Still to come in this chapter

02AVG drops the rows it cannot use
03NOT IN meets one NULL and returns nothing at all
04The join that deletes rows
05Say which one you meant
06Now do it where you cannot see the rows