SQL Conditional Aggregation

Aggregate only rows matching a condition, WITHIN each group, using CASE inside the aggregate — the classic technique for splitting one column into several conditional columns.

What & Why

Conditional aggregation places a CASE WHEN expression inside an aggregate so one grouped query can calculate metrics for different subsets of the same rows.

COUNT(CASE WHEN qualified_at IS NOT NULL THEN 1 END) counts a lead only when the condition is true. Otherwise CASE returns NULL implicitly and COUNT skips it.

See How It Works

BUSINESS QUESTION

Marketing wants total, qualified, and converted leads for every acquisition source.

id	campaign_id	email	created_at	qualified_at	converted_at	lead_score	source	country
301	1	ana@example.com	2024-01-21 09:10:00+00	2024-01-22 11:00:00+00	2024-02-02 10:00:00+00	86	google_ads	US
302	1	ben@example.com	2024-01-24 12:40:00+00	NULL	NULL	52	google_ads	CA
303	2	chloe@example.com	2024-02-16 08:30:00+00	2024-02-18 14:20:00+00	NULL	74	email	GB
304	3	dev@example.com	2024-03-20 17:15:00+00	2024-03-21 09:00:00+00	2024-04-04 16:00:00+00	91	linkedin	US

Watch each marketing.leads row update its source metricsLead 1 of 4

COUNT(*) · COUNT(CASE WHEN qualified_at IS NOT NULL THEN 1 END) · COUNT(CASE WHEN converted_at IS NOT NULL THEN 1 END)

id	source	qualified_at	converted_at	state
301	google_ads	2024-01-22 11:00:00+00	2024-02-02 10:00:00+00	CURRENT
302	google_ads	NULL	NULL	WAIT
303	email	2024-02-18 14:20:00+00	NULL	WAIT
304	linkedin	2024-03-21 09:00:00+00	2024-04-04 16:00:00+00	WAIT

source	leads	qualified_leads	converted_leads
google_ads	1	1	1

Lead 301 increments total, qualified, and converted for google_ads.

EXAMPLE QUERY

SELECT
  source,
  COUNT(*) AS leads,
  COUNT(CASE WHEN qualified_at IS NOT NULL THEN 1 END) AS qualified_leads,
  COUNT(CASE WHEN converted_at IS NOT NULL THEN 1 END) AS converted_leads
FROM marketing.leads
GROUP BY source
ORDER BY leads DESC, source;

Now You Try

Practice this concept

Marketing wants total, qualified, and converted lead counts for every source.

Available schema

marketing

Prefix tables with marketing.table_name.

sourceleadsqualified_leadsconverted_leads

marketing.leads

Column	Type
id	integer
campaign_id	integer
email	text
created_at	timestamp with time zone
qualified_at	timestamp with time zone
converted_at	timestamp with time zone
lead_score	integer
source	text
country	text
archive_status	text

query.sql

Intermediate business practice