Calculated field
{ 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, 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, 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, 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 | 590 |
| North | Pine | 590 |
| South | Ash | 400 |
| South | Elm | 400 |
Same rows from the calculation and from the SQL in DuckDB.
Where a naive translation goes wrong
- Keeping Team in GROUP BY returns team subtotals instead of the enclosing region total.
Naive SQL
SELECT "Region", "Team", SUM(Revenue) AS result FROM input GROUP BY "Region", "Team" ORDER BY "Region", "Team"
Region Team result North Cedar 500 North Pine 90 South Ash 100 South Elm 300 Matches the calculation
SELECT Region, Team, 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 590 North Pine 590 South Ash 400 South Elm 400
Signature
{ EXCLUDE [Team] : SUM([Revenue]) }