Quick answer: COUNT(expression) counts non-null results, including zero. If CASE returns 1 for success and 0 otherwise, every row counts. For a conditional count, omit ELSE so nonmatches return NULL, or use SUM with explicit 1 and 0 values.

By Prakhar Shrivastava · October 9, 2026

Define the population first

A fictional US/UK service records six completed processing attempts. Each event_id identifies one attempt; the export is complete, but one outcome is missing. This is an attempt-level report, not a count of unique users or customers. Repeated attempts by the same customer would remain separate.

The US has one known success, one failure and one unknown outcome. The UK has two successes and one failure. We will report known successes divided by all recorded attempts, while displaying unknown outcomes separately. This definition does not classify an unknown outcome as a confirmed failure.

Compare the wrong and correct counts

Run this self-contained example. The result checks were executed in SQLite; the CASE and aggregate syntax also follows the PostgreSQL references below.

WITH events(event_id, market, status) AS (
  VALUES (1,'US','success'), (2,'US','failed'), (3,'US',NULL),
         (4,'UK','success'), (5,'UK','success'), (6,'UK','failed')
)
SELECT market, COUNT(*) AS attempts,
  COUNT(CASE WHEN status='success' THEN 1 ELSE 0 END) AS wrong_count,
  COUNT(CASE WHEN status='success' THEN 1 END) AS successes,
  SUM(CASE WHEN status='success' THEN 1 ELSE 0 END) AS success_sum,
  COUNT(status) AS known_statuses
FROM events
GROUP BY market
ORDER BY market;

market  attempts  wrong_count  successes  success_sum  known_statuses
UK             3            3          2            2               3
US             3            3          1            1               2

The wrong count reports three successes in each market. The correct COUNT skips the NULL that CASE returns for a nonmatch. SUM instead adds the explicit 1/0 flags. Neither expression infers the missing US outcome.

Which denominator answers your question?

Using the same events CTE, replace the SELECT with:

SELECT market,
  COUNT(*) AS attempts,
  COUNT(*) - COUNT(status) AS unknown_outcomes,
  100.0 * COUNT(CASE WHEN status='success' THEN 1 END)
    / NULLIF(COUNT(*), 0) AS known_success_pct_of_attempts
FROM events
GROUP BY market
ORDER BY market;

The UK rate is approximately 66.67%; the US rate is approximately 33.33%, with one unknown outcome. Dividing the US success count by COUNT(status) would instead produce 50%: success among the two known outcomes. Both calculations can be described accurately, but they answer different questions. Do not silently switch denominators to make a rate look better.

The 100.0 factor preserves fractional results in this example. For another common failure, see SQL integer division and rates that become 0%. When combining markets, add successes and attempts before dividing; see the weighted conversion-rate exercise.

What happens when there are no rows?

For an aggregate query without GROUP BY over an empty input, COUNT returns 0 while SUM returns NULL. Therefore the two conditional-count forms are not interchangeable in every situation. With GROUP BY, an absent market produces no output group at all. A reporting calendar or market scaffold may be needed to show it.

Only replace an absent count with zero when the extract is complete and zero is meaningful. A missing feed is not proof that no attempts occurred. For missing-measure reasoning, use the NULL-versus-zero exercise.

Three checks before using the metric

• Check the event identity. A retry callback or a join can duplicate event rows. Correct that using an agreed event key before counting; CASE does not deduplicate data.
• Check the status vocabulary. This fixture allows success, failed or NULL. In real data, unexpected strings, blank strings and inconsistent capitalization need validation. COUNT(status) treats a non-null unexpected string as known.
• Check the grain after joins. If a LEFT JOIN preserves a market with no matching event, COUNT(*) counts its placeholder row. Count the non-null event key for the denominator instead. See retaining zero-activity campaigns.

A useful explanation is: “COUNT counts non-null expressions, not truth. I will count a value only for matching events, report unknown outcomes separately, and name the denominator before interpreting the percentage.”

Primary references

PostgreSQL aggregate functions documents COUNT, SUM and empty-input behavior. PostgreSQL conditional expressions explains CASE and its default NULL result when no branch matches and ELSE is omitted.

Return to the SQL grouping guide for more aggregation practice. The data here is fictional and is not a verified employer interview question.