Chapter 04 · 7 min · Basic
Counting things correctly
Counting is the first thing you learn and the last thing you get right.
The reason is that SQL has several different counts and they are one word apart. count(*) counts rows. count(score) counts rows where score is not null. count(distinct rider_name) counts different people. On a clean table all three agree, which is why nobody notices; on a real table they are three different numbers and each one answers a different question.
The same goes for avg. It looks like it averages a column. What it actually does is add up the values it has and divide by how many it has — and it never mentions the rows it skipped.
By the end of this chapter you will produce several defensible answers to "how are we scoring" and, more importantly, be able to say which one you meant.
The dataset
Fourteen feedback responses from a ride-hailing app. Two things were built in deliberately and both are completely ordinary: some responses have no score (the person opened the form and never rated), and two have no city. Aisyah appears three times, because people give feedback more than once.
Schema
| feedback_id | int |
| rider_name | text |
| city | text |
| score | int |
| channel | text |
Example data
Three counts, one table
Start with the question that cannot possibly go wrong: how many responses are there?
count(*) counts rows — all fourteen, blanks and repeats included. It is the only count that never skips anything.
count(score) counts rows where score is not null. That is not an edge case or a setting; it is how every aggregate in SQL except count(*) behaves. Nulls are not counted, not summed, not averaged. They are dropped, silently, with no warning and no error.
count(distinct rider_name) counts different people, so Aisyah's three responses contribute one.
So "how many" already has three true answers — fourteen responses, nine ratings, twelve people — and the only thing separating them is what you typed inside the brackets. Say which one you mean, in words, before you pick the syntax.
Fourteen rows. Which of the other two numbers is smaller, and by how much?
Basic
The rest of this chapter is Basic
Learning SQL is free here, forever. This track is the paid half: what to actually do with a dataset once somebody hands you one, from the first look to a number you can defend.
RM 25/mo · cancel anytime
Still to come in this chapter
- 02DISTINCT skips the blanks too
- 03AVG picks a denominator for you
- 04Report the coverage beside the number
- 05Refuse to report the thin ones
- 06The sentence to attach