SQL / AGGREGATION WITH GROUP BY
Counting rows without fooling yourself
Tell COUNT(*), COUNT(column) and conditional counts apart, and get honest numbers when NULLs, joins and empty groups are in play.
What you will learn
- Use COUNT(*) for rows and COUNT(col) for non-NULL values; the gap is the NULL count.
- After a LEFT JOIN, count the child key so unmatched parents report 0, not 1.
- Count a condition with COUNT(CASE WHEN cond THEN 1 END), never COUNT(cond).
- Recognise that a filtered-away group vanishes instead of reporting zero.
Understanding Counting rows without fooling yourself
COUNT(*) and COUNT(rating) answer different questions, and the difference between them is exactly the number of NULLs. COUNT(*) counts the rows that reached the aggregate without looking at any value, while COUNT(expr) evaluates expr once per row and counts the results that are not NULL. That is why COUNT(1) and COUNT('x') are identical to COUNT(*): a constant is never NULL. Reading COUNT(rating) as "number of reviews" is the quietest way to be wrong, because the query still runs and the number still looks reasonable.
The second trap is that a row stops meaning one thing the moment you join. If a book has three reviews, the joined result holds three rows for that book, so COUNT(*) is counting review rows even if you named the column book_count. A LEFT JOIN adds the mirror problem: an unmatched book survives as a single row padded with NULLs, and COUNT(*) faithfully counts that row as 1. Counting a column from the child table instead, such as COUNT(r.id), fixes it because on the padded row that expression is NULL.
The third trap is that a group exists only if some row produced it. WHERE runs before grouping, so filtering away every row for book 12 does not give you a 0 for book 12 — it removes book 12 from the result entirely, and no COALESCE on the outside can bring it back. The one exception is an aggregate with no GROUP BY at all: that query always returns exactly one row, and COUNT there returns 0 rather than NULL, which is why a scalar COUNT subquery is safe to compare against 0.
CREATE TABLE reviews (
id INTEGER PRIMARY KEY,
book_id INTEGER,
rating INTEGER
);
INSERT INTO reviews (id, book_id, rating) VALUES
(1, 10, 5),
(2, 10, NULL),
(3, 10, 4),
(4, 11, NULL);
SELECT COUNT(*) AS rows_total,
COUNT(rating) AS ratings_present,
COUNT(*) - COUNT(rating) AS ratings_missing,
COUNT(1) AS also_rows
FROM reviews;COUNT(*) counts rows that reached the aggregate while COUNT(expr) counts rows where expr is not NULL, and nearly every counting bug is a confusion between those two.
Worked examples
Zeros after a LEFT JOIN
Shows why COUNT(*) reports 1 for a parent row that matched nothing, and what to count instead.
CREATE TABLE books (id INTEGER, title TEXT);
CREATE TABLE reviews (id INTEGER, book_id INTEGER);
INSERT INTO books VALUES (10, 'Deep Work'), (11, 'Quiet'), (12, 'Flow');
INSERT INTO reviews VALUES (1, 10), (2, 10), (3, 11);
SELECT b.title,
COUNT(*) AS star_count,
COUNT(r.id) AS review_count
FROM books b
LEFT JOIN reviews r ON r.book_id = b.id
GROUP BY b.title
ORDER BY b.title;Example explained
Line 1LEFT JOIN keeps 'Flow' by emitting one row with r.id and r.book_id set to NULL.
Line 2COUNT(*) counts that padded row, so a book with no reviews is reported as having 1.
Line 3COUNT(r.id) evaluates r.id per row and skips the NULL, giving the honest 0.
Line 4'Deep Work' matched two review rows, so both forms agree at 2 — they only diverge where matches are missing.
Counting a condition
Demonstrates that COUNT tests for NULL rather than for truth, which is what makes CASE-based counting work.
CREATE TABLE reviews (id INTEGER, book_id INTEGER, rating INTEGER);
INSERT INTO reviews VALUES
(1, 10, 5), (2, 10, 2), (3, 10, NULL),
(4, 11, 4), (5, 11, 5);
SELECT book_id,
COUNT(*) AS reviews,
COUNT(CASE WHEN rating >= 4 THEN 1 END) AS positive,
COUNT(CASE WHEN rating >= 4 THEN 0 END) AS positive_again
FROM reviews
GROUP BY book_id
ORDER BY book_id;Example explained
Line 1The CASE has no ELSE, so ratings below 4 and NULL ratings both fall through to NULL and are not counted.
Line 2THEN 0 gives the same answer as THEN 1, because 0 is a value; COUNT only asks whether the result is NULL.
Line 3book_id 10 has three rows but only one rating of 4 or more, so positive is 1 while reviews is 3.
Line 4Adding ELSE 0 would silently break the query: every row would then produce a non-NULL value and positive would equal reviews.
Zero rows versus no group
Contrasts a count over no rows with a grouped count over no rows.
CREATE TABLE reviews (id INTEGER, book_id INTEGER, rating INTEGER);
INSERT INTO reviews VALUES (1, 10, 5), (2, 10, 4);
SELECT COUNT(*) AS n
FROM reviews
WHERE book_id = 99;
SELECT book_id, COUNT(*) AS n
FROM reviews
WHERE book_id = 99
GROUP BY book_id;Example explained
Line 1The first query has no GROUP BY, so it is a single aggregate over an empty input and still returns exactly one row.
Line 2COUNT over zero rows is 0 and never NULL, which is why comparing a scalar COUNT subquery to 0 is reliable.
Line 3The second query groups by book_id, and with no surviving rows there is no group to emit at all.
Line 4So the answer to "how many reviews does book 99 have" is absent rather than 0 — a report that must show 0 has to start from the table holding the keys.
Important notes
COUNT(*), COUNT(1) and COUNT('x') compile to the same work in Postgres, MySQL and SQLite; the choice buys no speed, so prefer COUNT(*) because it reads as "rows".
In a LEFT JOIN count, a condition on the child table belongs in the ON clause; in WHERE it discards the padded rows and the unmatched parents disappear along with their zeros.
Common mistakes
Writing COUNT(column) when you meant "all rows": the query succeeds but undercounts by exactly the number of NULLs, so a dashboard quietly loses rows nobody audits.
Using COUNT(*) with a LEFT JOIN: every parent with no children reports 1 instead of 0, which inflates totals and makes empty categories look active.
Writing COUNT(rating >= 4) instead of COUNT(CASE WHEN rating >= 4 THEN 1 END): the comparison produces a value, not a filter, so true and false rows are both counted and you get the row total back.
Try it yourself
Change, predict, then run
Create an authors table and a books table where one author has no books at all, then write a single grouped query that lists every author with a books_written value, including 0 for that author. Verify your result by checking that the counts sum to COUNT(*) on books.
Open the SQL workspaceCheck your understanding
A customers LEFT JOIN orders query grouped by customer shows COUNT(*) = 1 for Mira, who has never placed an order. Why?
- The outer join emits one row for Mira with NULLs in the order columns, and COUNT(*) counts rows regardless of their contents.
- COUNT(*) treats a NULL order id as zero and then adds one to it.
- GROUP BY is required to emit at least one row per group, so the minimum any count can report is 1.
- The join matched one order whose id happens to be NULL, so it belongs to Mira.
Show answer
The LEFT JOIN preserves Mira by producing a real row padded with NULLs, and COUNT(*) never inspects values, so that row counts as 1; switching to COUNT(o.id) returns 0 because that expression is NULL on the padded row. The second option is tempting but backwards: COUNT(*) does not look at the order id at all, and it never converts NULL to a number.