COUNT(*) vs COUNT(column): Why Your Counts Differ

The lab has five members. Ada and Grace share an address, Linus has another, and two members have NULL in email. The first query displays NULL as [NULL] so you can see the missing values. COALESCE changes only the display; the stored values stay untouched.

The second query asks four different questions:

The shared address counts twice in COUNT(email), once in COUNT(DISTINCT email). Neither counts NULL email values. An empty string, however, is a non-null text value and would count. Whether it is a usable address is a separate validation rule.

Avoid replacing the missing values inside the count just to make the numbers agree. COUNT(COALESCE(email, 'unknown')) would count all five members, because the replacement makes every expression result non-null. That changes the question.

psql

SELECT id, name, team, COALESCE(email, '[NULL]') AS email
FROM members ORDER BY id;

SELECT COUNT(*) AS members, COUNT(email) AS email_values,
       COUNT(*) - COUNT(email) AS missing_emails,
       COUNT(DISTINCT email) AS distinct_emails
FROM members;

GROUP BY applies the same counting rules separately to each team. Engineering has three members, but only two email values. Those two values are the same address, so its distinct count is one.

This matters when naming a report column. A label such as “members” should count member rows; “email addresses” could mean populated values or unique addresses. Pick the expression that matches the metric and give it a precise alias.

WHERE runs before grouping. If you add WHERE email IS NOT NULL, the two members without email disappear before any count is computed. COUNT(*) then counts only the remaining members, and no longer measures the full team size.

SELECT team, COUNT(*) AS members, COUNT(email) AS email_values,
       COUNT(DISTINCT email) AS distinct_emails
FROM members
GROUP BY team
ORDER BY team;

Now build a support report that includes members who have no tickets. Ada has two tickets, Grace has one, and the other three members have none. A nullable closed_at timestamp records whether a ticket has been closed.

LEFT JOIN preserves every member. For a member without a matching ticket it produces one output row with NULL in the ticket columns. COUNT(*) counts that row, giving a misleading “one ticket” if you use it as the ticket total.

Count t.id instead. It is the ticket table's primary key, so it is non-null for every real ticket and NULL on the unmatched join row. COUNT(t.closed_at) answers another question again: how many of those tickets have a closing timestamp? Grace's ticket exists even though its closed_at is NULL.

You do not need COALESCE around COUNT(t.id) to get zero here. The preserved member group exists, and none of its t.id values is non-null.

If you later join another one-to-many table, inspect the joined rows again: each ticket may appear multiple times. Counting a non-null key solves the unmatched-row problem, but does not remove duplicated matches.

SELECT m.name, COUNT(*) AS joined_rows,
       COUNT(t.id) AS tickets, COUNT(t.closed_at) AS closed_tickets
FROM members m
LEFT JOIN tickets t ON t.member_id = m.id
GROUP BY m.id, m.name
ORDER BY m.id;

WHERE false deliberately removes every input row. Without GROUP BY, the aggregate query still produces one summary row: both counts are zero.

With GROUP BY team there are no team groups to summarize, so the second query produces no rows. This is different from the LEFT JOIN example, where each member was preserved and therefore had a group.

If a dashboard needs every team displayed even when no members match a filter, start from a table of teams and preserve it with a LEFT JOIN. Count a non-null member key, just as you counted the ticket key in the previous example.

SELECT COUNT(*) AS members, COUNT(email) AS email_values
FROM members
WHERE false;

SELECT team, COUNT(*) AS members
FROM members
WHERE false
GROUP BY team;