COUNT(*) and COUNT(column) give me different numbers

Question

For my budgeting app I want the number of expenses per category, including categories with none. This shows 1 for empty categories instead of 0:

SELECT c.name, COUNT(*) AS expenses
FROM categories c
LEFT JOIN expenses e ON e.category_id = c.id
GROUP BY c.name;

Someone said to use COUNT(e.id) and that does give 0. But why are they different at all?

Hannah Lindqvist · Accepted Answer

They count different things:

COUNT(*) counts rows.
COUNT(expression) counts rows where that expression is not NULL.

A category with no expenses still produces one row from the LEFT JOIN, with every e. column set to NULL. COUNT(*) sees that row and says 1. COUNT(e.id) sees a NULL and skips it, so it says 0, which is the answer you want.

Count a column from the joined table that can never be NULL in a real match. The primary key is the safe choice.

Leilani Kahale · Answer

So the LEFT JOIN row is real, it is just full of NULLs. That explains a couple of other weird totals I had too. Thank you!

Priya Nair · Answer

And the third one you will meet soon: COUNT(DISTINCT e.merchant) counts different non-NULL values, so "how many shops did we spend at" rather than "how many purchases".