SQL Percent of Total
Each row's share of the whole — every percentage in the result adds up to exactly 100%.
What & Why
Dividing each row's value by the SUM OVER() of the whole group gives that row's percentage of the total — a single line of SQL, no separate query to compute the denominator.
See How It Works
BUSINESS QUESTION
Calculate each campaign's percentage of total marketing spend.
| 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 every campaign divide by the same total spendStep 1 of 4
spend / SUM(spend) OVER () * 100
| name | channel | spend | total_spend | spend_share_pct |
|---|---|---|---|---|
| Enterprise Search | google_ads | 60,000.00 | 210,000.00 | 28.57 |
| Spring Launch | google_ads | 55,000.00 | 210,000.00 | 26.19 |
| Finance Retargeting | 50,000.00 | 210,000.00 | 23.81 | |
| Retention Webinar | 45,000.00 | 210,000.00 | 21.43 |
Enterprise Search contributes 28.57% of 210,000.
EXAMPLE QUERY
SELECT
name,
channel,
spend,
ROUND(spend::numeric / NULLIF(SUM(spend) OVER (), 0) * 100, 2) AS spend_share_pct
FROM marketing.campaigns
ORDER BY spend_share_pct DESC, name;This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario