SQL OVER Clause
The one keyword that turns a normal aggregate into a window function.
What & Why
The OVER clause defines the rows available to a window function while leaving detail rows in the result. It separates a calculation's context from GROUP BY so totals, ranks, and comparisons can appear beside each row.
See How It Works
BUSINESS QUESTION
Compare a plain campaign-spend aggregate with full-table and channel-partitioned OVER results.
| 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 | 45000.00 | 2024-02-10 | 2024-04-15 | active | enterprise | |
| 3 | Finance Retargeting | 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 |
Watch OVER change the aggregate result grainStep 1 of 3
SUM(spend) · no OVER
| total_campaign_spend |
|---|
| 210,000.00 |
A plain aggregate collapses all four campaign rows into one total row.
EXAMPLE QUERY
-- Plain aggregate: one result row
SELECT SUM(spend) AS total_campaign_spend
FROM marketing.campaigns;
-- Empty OVER: repeat the full total beside every campaign
SELECT
id AS campaign_id,
name,
spend,
SUM(spend) OVER () AS total_campaign_spend
FROM marketing.campaigns
ORDER BY spend DESC, campaign_id;
-- PARTITION BY: repeat a separate total inside each channel
SELECT
id AS campaign_id,
name,
channel,
spend,
SUM(spend) OVER (PARTITION BY channel) AS channel_spend
FROM marketing.campaigns
ORDER BY channel, spend DESC, campaign_id;This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario