Why COUNT(*) and COUNT(column) Give Different Answers in SQL

Same table, two counts, two different numbers

SELECT COUNT(*) FROM employees returns 20. SELECT COUNT(manager_id) FROM employees, against the exact same 20 rows, returns 15. Nothing was deleted in between, and neither query has a WHERE clause. Run them side by side:

The gap is five, and it's not a bug: five employees sit at the top of their department's org chart and have no manager, so manager_id is NULL for those rows. COUNT(*) counts rows, full stop, NULL or not. COUNT(column) counts only the rows where that specific column is non-NULL. Same table, same rows, two different questions, two different answers. That single rule (NULL or not, per column) is also why AVG() can quietly disagree with a count you'd expect it to agree with, which is the part most explanations of COUNT stop short of showing.

The rule holds inside every group, not just the whole table

GROUP BY doesn't change the rule, it just applies it once per group. Split the same comparison by department:

Every department is short by exactly one, because every department has exactly one person at the top with no manager. If you'd used COUNT(manager_id) as a stand-in for headcount here, you'd have understaffed every department in the report by one person, silently.

A third number: COUNT(DISTINCT column)

NULL-skipping isn't the only thing that changes what COUNT returns. DISTINCT applies a completely separate rule: collapse duplicates before counting, regardless of NULLs.

department_id has no NULLs, so the first two columns match at 20. The third drops to 5, because DISTINCT doesn't care that department 1 appears five times, it counts department 1 once. COUNT(*), COUNT(column), and COUNT(DISTINCT column) are three genuinely different operations that happen to share a function name; picking the wrong one doesn't error, it just answers a different question than the one you meant to ask.

The one nobody warns you about: AVG's denominator isn't COUNT(*)

Here's where this stops being a curiosity and starts breaking real reports. AVG(column) is defined as SUM(column) / COUNT(column), not SUM(column) / COUNT(*). If you ever try to hand-roll an average and divide by COUNT(*) instead, you get a different, lower number, and nothing tells you it's wrong.

This shows up hardest after a LEFT JOIN, because that's where NULLs get introduced into rows that didn't have any to begin with. Three students in the students table have never been graded, so joining to grades gives them a row with a NULL score instead of dropping them:

total_rows is 33: 30 real grade rows plus one NULL-score row for each of the three ungraded students. AVG(g.score) correctly divides the sum of actual scores by 30, the number of rows that actually have a score, giving 84.7. naive_avg divides that same sum by COUNT(*), all 33 rows, including the three that contributed nothing to the sum, and comes out to 77.0. Same data, same SUM, a nearly eight-point gap, because one formula used the right denominator and the other didn't.

Building your own COUNTIF

SQL has no COUNTIF. The standard way to count rows matching a condition reuses the exact rule from the top of this post: wrap the condition in a CASE that returns NULL for rows that don't match, then let COUNT skip them for free.

CASE WHEN status = 'completed' THEN 1 END has no ELSE, so every row that isn't completed evaluates to NULL instead of some placeholder value. COUNT then does what it always does with a column argument: it counts the non-NULLs and ignores the rest. You'll also see this written as SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END), which gets the same number through addition instead of NULL-skipping, but the COUNT version is shorter and leans on the rule you already need to know.

Where to go next

The COUNT reference and AVG reference cover each function's full syntax if you need the basics on their own, and understanding NULL is the underlying concept both of them build on. The aggregating DISTINCT values example extends the same COUNT(DISTINCT ...) idea to SUM and AVG, which have the same "collapse duplicates first" behavior shown above. The practice problems are worth trying once you want to catch yourself picking the wrong one of these three under real query pressure, without a worked example already in front of you.