Calculated field
SUM([Revenue]) / { EXCLUDE [Team] : SUM([Revenue]) }
Sample data
| Region | Team | Customer | Revenue | Seats | Date |
|---|---|---|---|---|---|
| North | Cedar | Aster | 120 | 4 | 2026-01-05 |
| North | Cedar | Aster | 80 | 2 | 2026-02-05 |
| North | Cedar | Birch | 300 | 6 | 2026-01-12 |
| North | Pine | Clover | 90 | 3 | 2026-02-14 |
| South | Elm | Dahlia | 200 | 5 | 2026-01-08 |
| South | Elm | Dahlia | 100 | 2 | 2026-02-08 |
| South | Ash | Fern | 75 | 3 | 2026-01-20 |
| South | Ash | Fern | 25 | 1 | 2026-02-20 |
The same calculation as SQL
SELECT Region, Team, SUM(Revenue)*1.0/MAX(total) FROM input JOIN (SELECT Region, SUM(Revenue) total FROM input GROUP BY Region) USING(Region) GROUP BY Region, Team ORDER BY Region, Team
Runs the example above.
SELECT Region, Team, SUM(Revenue)*1.0/MAX(total) FROM input JOIN (SELECT Region, SUM(Revenue) total FROM input GROUP BY Region) USING(Region) GROUP BY Region, Team ORDER BY Region, Team
Written for this dialect; not run here.
SELECT Region, Team, SUM(Revenue)*1.0/MAX(total) FROM input JOIN (SELECT Region, SUM(Revenue) total FROM input GROUP BY Region) USING(Region) GROUP BY Region, Team ORDER BY Region, Team
Written for this dialect; not run here.
Result
| Region | Team | result |
|---|---|---|
| North | Cedar | 0.847458 |
| North | Pine | 0.152542 |
| South | Ash | 0.25 |
| South | Elm | 0.75 |
Same rows from the calculation and from the SQL in DuckDB.
Where a naive translation goes wrong
- Dividing by the team subtotal produces one instead of the team share of regional revenue.
Naive SQL
SELECT "Region", "Team", SUM(Revenue)*1.0/SUM(Revenue) AS result FROM input GROUP BY "Region", "Team" ORDER BY "Region", "Team"
Region Team result North Cedar 1 North Pine 1 South Ash 1 South Elm 1 Matches the calculation
SELECT Region, Team, SUM(Revenue)*1.0/MAX(total) FROM input JOIN (SELECT Region, SUM(Revenue) total FROM input GROUP BY Region) USING(Region) GROUP BY Region, Team ORDER BY Region, Team
Region Team result North Cedar 0.847458 North Pine 0.152542 South Ash 0.25 South Elm 0.75
Signature
SUM([Revenue]) / { EXCLUDE [Team] : SUM([Revenue]) }