SQL Conditional Aggregation: SUM and COUNT With CASE
By Sharon Ben-Moshe · August 21, 2026 · 2 min read
Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.
Use CASE inside SUM and COUNT to calculate several conditional metrics in one grouped SQL query. Learn the pattern, NULL behavior, and pitfalls.
Quick answer
SQL conditional aggregation places a CASE expression inside an aggregate. `SUM(CASE WHEN condition THEN amount ELSE 0 END)` totals only matching rows, while `COUNT(CASE WHEN condition THEN 1 END)` counts matching rows because COUNT ignores NULL. It produces multiple metrics per group without losing the group’s other rows.
FAQs
What is conditional aggregation in SQL?
Conditional aggregation uses an aggregate function around a conditional expression, often CASE. It lets one grouped query calculate multiple metrics that each include a different subset of rows.
Why is CASE inside SUM?
Putting CASE inside SUM evaluates the condition for each input row, then sums the selected numeric values. That is what makes the condition local to that one metric rather than filtering the entire query.
How do I count rows with a condition in SQL?
A common pattern is `COUNT(CASE WHEN condition THEN 1 END)`. COUNT ignores NULL, so rows that do not meet the condition do not contribute a count.
Should SUM use ELSE 0 or ELSE NULL?
Use ELSE 0 when non-matching rows should contribute zero to a total. Use ELSE NULL when the distinction between no value and a numeric zero matters to the report; test the intended output explicitly.
Keep reading
SQL BETWEEN Is Inclusive: Safer Date Range Queries
By Sharon Ben-Moshe · Aug 21, 2026
SQL BETWEEN includes both endpoints. Learn the timestamp boundary trap and use half-open date ranges that stay correct across databases.
Read postSQL DELETE vs TRUNCATE vs DROP: Choose Safely
By Sharon Ben-Moshe · Aug 21, 2026
DELETE removes selected rows, TRUNCATE empties a table, and DROP removes the table itself. Learn the crucial differences before running destructive SQL.
Read postTry it now
Paste your SQL into sqlfmt — format, validate, and catch typos free in your browser.