SQL COUNT(*) vs. COUNT(column): Why Does NULL Reduce the Count?

There are four jobs, but the count of jobs with a recorded retry count is three. Did a job disappear? More likely, the two queries counted different things. COUNT(*) counts rows in the result, while COUNT(column) counts non-NULL values in that column.

What do COUNT(*) and COUNT(column) count?

Of the four jobs below, only job 3 has a NULL retry_count. Job 1 has 0: that is a recorded value meaning no retries, not a missing value.

Across the same four rows, COUNT(*) counts every row and COUNT(retry_count) excludes the one NULL value. Both counts include the row whose recorded value is 0.

CREATE TABLE jobs (
  id INTEGER PRIMARY KEY,
  retry_count INTEGER
);

INSERT INTO jobs (id, retry_count) VALUES
  (1, 0), (2, 2), (3, NULL), (4, 1);

SELECT COUNT(*) AS total_jobs,
       COUNT(retry_count) AS recorded_jobs
FROM jobs;

The asterisk in COUNT(*) tells SQL to count the result rows. COUNT(retry_count) counts rows with a value in that column instead. It includes 0, 2, and 1, but excludes the one NULL.

How can you find the job with a NULL value?

For the same set of input rows, the difference between these counts is the number of rows where that column is NULL.

SELECT COUNT(*) - COUNT(retry_count) AS missing_retry_count
FROM jobs;
-- 1

SELECT id FROM jobs WHERE retry_count IS NULL;
-- 3

missing_retry_count = 1 gives the number of missing values; id = 3 identifies the job missing that value. Treating zero as missing would give the wrong answer.

Why can a WHERE clause make the counts equal?

If WHERE removes rows with a NULL retry count first, all remaining rows have a value to count.

SELECT COUNT(*) AS rows_after_filter,
       COUNT(retry_count) AS recorded_after_filter
FROM jobs
WHERE retry_count IS NOT NULL;
-- 3 | 3

When a count differs from what you expect, do not look only inside COUNT(...). Check whether the queries count the same FROM and WHERE result. If a join duplicates rows, even COUNT(*) counts the expanded result rows rather than the original jobs.

Key takeaways

COUNT(*) counts every result row; COUNT(column) skips NULL values in that column. Zero is counted. To investigate the difference, use COUNT(*) - COUNT(column) to count missing values and IS NULL to find the corresponding rows.