SQL Window Function vs GROUP BY

Same aggregate math — one keeps every row, the other collapses them.

What & Why

GROUP BY and a window function can compute the exact same average, sum, or count — the only difference is whether the individual rows survive in the output.

See How It Works

BUSINESS QUESTION

Compare campaign-level channel counts with a collapsed channel summary.

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

Compare preserved detail with grouped result grainStep 1 of 2

COUNT(*) OVER (PARTITION BY channel)

campaign_id	name	channel	channel_campaigns
2	Retention Webinar	email	1
1	Spring Launch	google_ads	2
4	Enterprise Search	google_ads	2
3	Finance Retargeting	linkedin	1

The window keeps four campaign rows and adds each channel's count beside them.

EXAMPLE QUERY

-- Preserve every campaign row
SELECT
  id AS campaign_id,
  name,
  channel,
  COUNT(*) OVER (PARTITION BY channel) AS channel_campaigns
FROM marketing.campaigns
ORDER BY channel, campaign_id;

-- Collapse to one row per channel
SELECT
  channel,
  COUNT(*) AS channel_campaigns
FROM marketing.campaigns
GROUP BY channel
ORDER BY channel;

This lesson's practice is part of Pro.

See what's included →

Advanced business practice