-- CASE pattern

SELECT

department,

COUNT(*) AS total,

SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_count,

SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) AS inactive_count

FROM employees

GROUP BY department;

-- FILTER pattern (PostgreSQL)

SELECT

department,

COUNT(*) AS total,

COUNT(*) FILTER (WHERE status = 'active') AS active_count,

COUNT(*) FILTER (WHERE status = 'inactive') AS inactive_count

FROM employees

GROUP BY department;

-- Revenue breakdown

SELECT

customer_id,

SUM(CASE WHEN status = 'completed' THEN order_total ELSE 0 END) AS completed_revenue,

SUM(CASE WHEN status = 'pending' THEN order_total ELSE 0 END) AS pending_revenue

FROM orders

GROUP BY customer_id;

SUM(CASE WHEN condition THEN 1 ELSE 0 END) creates conditional counters

SUM(CASE WHEN condition THEN value ELSE 0 END) creates conditional sums

ELSE 0 crucial - without it, NULL breaks aggregation

FILTER (WHERE condition) cleaner where supported

Multiple conditional aggregates in same SELECT

Works with any aggregate: COUNT, SUM, AVG, MIN, MAX

Core references in this topic include CASE, COUNT, SUM. Learn what each one does, when to use it, and the execution or engine rules that matter.

CASE

Adds branching logic directly inside SQL expressions.

CASE WHEN salary > 100000 THEN 'High' ELSE 'Standard' END

COUNT

Counts rows or non-NULL values depending on the argument.

COUNT(*)

SUM

Adds numeric values together across the current group or window frame.

SUM(revenue)

FILTER