The status column everyone forgets
Almost every real table has one of these: status, state, is_active,
deleted_at. Rows don't disappear when something changes in the real world -
an employee leaves, a subscription lapses, an order gets cancelled - the row
stays put and a column gets updated to say so. Nothing you've queried so far
has had one of these columns. This one does.
⚠️ Gotcha. COUNT(*) counts every row in the table, full stop. It has no
idea that status exists, let alone what value in it means "still here." A
table can hold rows for people who left the company two years ago, and
COUNT(*) will cheerfully add them to the head count anyway.
There's a new employees table: id, name, status. Four employees are
active, two are terminated, and one is on_leave.
Your task: how many employees do we currently have? Define "currently"
as status = 'active' - someone on_leave is still employed but isn't
counted here, and someone terminated obviously isn't either. The answer is
4.
You'll practice:
- Filtering with
WHERE before you aggregate with COUNT
- Noticing that a row existing in a table and a row still being "current" are two different things