SQL CASE with Aggregates

Wrap a CASE expression inside an aggregate function to summarize only the rows matching a condition — the exact technique the Grouping series introduced as conditional aggregation.

What & Why

This is the same conditional aggregation pattern from the Grouping & Aggregation series, revisited here from the CASE side rather than the aggregate-function side. The mechanics are identical: SUM(CASE WHEN condition THEN column ELSE 0 END) sums only the rows where condition is true, treating every other row as a contributed zero.

See How It Works

BUSINESS QUESTION

Marketing wants recipients and clicks for active campaigns only.

id	name	channel	spend	start_date	end_date	status	target_segment
1	Spring Launch	google_ads	55000.00	2024-01-15	2024-03-31	active	smb
2	Retention Webinar	email	45000.00	2024-02-10	2024-04-15	active	enterprise
3	Finance Retargeting	linkedin	50000.00	2024-03-12	2024-05-31	active	enterprise
4	Enterprise Search	google_ads	60000.00	2024-04-01	2024-06-30	active	enterprise

id	campaign_id	sent_at	recipients	opens	clicks	unsubscribes	bounces
201	1	2024-01-20 15:00:00+00	1200	540	180	9	24
202	2	2024-02-15 16:30:00+00	800	420	96	5	12
203	3	2024-03-18 13:00:00+00	1500	610	225	14	31
204	4	2024-04-08 14:00:00+00	0	0	0	0	0

EXAMPLE QUERY

SELECT
  c.channel,
  SUM(CASE WHEN c.status = 'active' THEN e.recipients ELSE 0 END) AS active_recipients,
  SUM(CASE WHEN c.status = 'active' THEN e.clicks ELSE 0 END) AS active_clicks
FROM marketing.campaigns c
JOIN marketing.email_sends e ON e.campaign_id = c.id
GROUP BY c.channel
ORDER BY active_recipients DESC, c.channel;

RESULT — exact output from the displayed Queryflo rows

channel	active_recipients	active_clicks
linkedin	1500	225
google_ads	1200	180
email	800	96

Each conditional SUM uses the real active campaign and email-send rows.

Now You Try

Practice this concept

Marketing wants recipients and clicks for active campaigns only.

Available schema

marketing

Prefix tables with marketing.table_name.

channelactive_recipientsactive_clicks

marketing.campaigns

Column	Type
id	integer
name	text
channel	text
spend	numeric
start_date	date
end_date	date
status	text
target_segment	text
legacy_id	text

query.sql

Intermediate business practice