Chapter 05 · 7 min · Basic
Missing is not zero
A blank cell and a zero look almost identical on screen and mean completely different things.
Zero is a measurement. Somebody counted the sambal jars and there were none.
Blank is the absence of a measurement. Nobody counted the kaya spread at all. There might be forty jars.
Confusing the two produces the most common bad number in analysis, and it does it silently: no error, no warning, just a total that is wrong and a filter that quietly drops rows you needed. This chapter is about telling them apart and then deciding — deliberately, in writing — what to do about each.
The dataset
Twelve items in a small café's stock list. stock_qty is null when the item was never counted and zero when it was counted and there was none left — last_counted is the proof of which is which. Four items have never been counted, and three of those also have no supplier on file.
Schema
| item_id | int |
| item_name | text |
| supplier | text |
| stock_qty | int |
| last_counted | date |
Example data
Look at the two kinds of empty
Sorting with nulls first pushes the blanks to the top so you can see them as a block rather than scattered through the list.
Read the top seven rows. Four items have a null quantity and a null last_counted — nobody has ever counted them. Three have a quantity of zero with a real count date — somebody stood there, looked at the shelf, and recorded that it was empty.
Those are different facts and they call for different actions. The zeroes are a reordering problem. The blanks are a stocktaking problem. Treating them the same means either ordering stock you already have, or not ordering stock you have run out of.
This pairing — a value column and the column that says whether it was measured — is worth looking for in every table. It is usually there, and it usually settles the question.
How many items have never been counted, and how many were counted at zero?
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
- 02Name the three states
- 03What blanks do to a total and an average
- 04The filter that eats your rows
- 05Say what you mean about the unknowns
- 06When COALESCE is honest