Conditional Aggregation: Three Queries Down to One

You are running three SELECTs to get three numbers from one table. One query does it, three times faster.

The problem

The report needs total exams, passes, and the pass rate. The usual approach is three separate queries stitched together by hand.

One query, one table scan

SELECT
  COUNT(*)                                          AS total,
  SUM(CASE WHEN result = 'pass' THEN 1 ELSE 0 END)  AS passes,
  ROUND(100.0 * SUM(CASE WHEN result = 'pass' THEN 1 ELSE 0 END)
        / COUNT(*), 1)                              AS pass_rate
FROM exams;

This is conditional aggregation. It reads the table once instead of three times.

Two equivalent forms

SUM(CASE WHEN result = 'pass' THEN 1 ELSE 0 END)
COUNT(CASE WHEN result = 'pass' THEN 1 END)

The second is shorter because COUNT already skips NULL, so no ELSE is needed. The first reads more clearly to whoever inherits the query.

Common mistakes

1. Adding ELSE to COUNT

-- WRONG: counts the failures too, always equals COUNT(*)
COUNT(CASE WHEN result = 'pass' THEN 1 ELSE 0 END)

Zero is still a value, so COUNT counts it. With COUNT, drop the ELSE; with SUM, keep ELSE 0.

2. Integer division

-- Returns 0 on PostgreSQL and MySQL
SELECT SUM(CASE WHEN result='pass' THEN 1 ELSE 0 END) / COUNT(*) FROM exams;

Integer divided by integer gives an integer. Multiply by 100.0 or cast to get decimals.

3. Forgetting the denominator can be zero

ROUND(100.0 * SUM(...) / NULLIF(COUNT(*), 0), 1)

NULLIF turns 0 into NULL, and dividing by NULL returns NULL rather than erroring.

Combined with GROUP BY

SELECT
  class,
  COUNT(*)                                            AS students,
  SUM(CASE WHEN score >= 8 THEN 1 ELSE 0 END)         AS top_marks,
  COUNT(DISTINCT CASE WHEN score >= 8 THEN student_id END) AS top_students,
  ROUND(AVG(CASE WHEN score >= 5 THEN score END), 2)  AS avg_of_passes
FROM scores
GROUP BY class;

That last line is worth noticing: AVG ignores NULL, so it averages only the passing scores without needing a separate WHERE.

Practice

• LeetCode 1934 — Confirmation Rate
• LeetCode 1211 — Queries Quality and Percentage
• LeetCode 262 — Trips and Users (hard, very much worth it)
• LeetCode 1907 — Count Salary Categories
• HackerRank — New Companies