SELECT department, SUM(CASE WHEN status = 'active' THEN 1 END) AS active_count FROM employees GROUP BY department;

SELECT department, SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_count FROM employees GROUP BY department;

Without ELSE 0, CASE returns NULL for non-matching rows. SUM of NULL returns NULL, not 0.

Always include ELSE 0 in conditional SUM expressions

High

Missing ELSE 0 causes NULL results

-- Multiple separate queries

SELECT department, COUNT(*) FROM employees WHERE status = 'active' GROUP BY department;

SELECT department, COUNT(*) FROM employees WHERE status = 'inactive' GROUP BY department;

SELECT department, SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active, SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) AS inactive FROM employees GROUP BY department;

Conditional aggregation provides complete picture in single efficient query.

Use conditional aggregation instead of multiple queries

High

Multiple queries slower and risk inconsistency