Calculated field
{ EXCLUDE [Region], [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", (SELECT SUM(Revenue) FROM input) AS result FROM input GROUP BY "Region", "Team" ORDER BY "Region", "Team"
Runs the example above.
SELECT "Region", "Team", (SELECT SUM(Revenue) FROM input) AS result FROM input GROUP BY "Region", "Team" ORDER BY "Region", "Team"
Written for this dialect; not run here.
SELECT `Region`, `Team`, (SELECT SUM(Revenue) FROM input) AS result FROM input GROUP BY `Region`, `Team` ORDER BY `Region`, `Team`
Written for this dialect; not run here.
Result
| Region | Team | result |
|---|---|---|
| North | Cedar | 990 |
| North | Pine | 990 |
| South | Ash | 990 |
| South | Elm | 990 |
Same rows from the calculation and from the SQL in DuckDB.
Where a naive translation goes wrong
- Grouping at the visible Region and Team grain does not exclude those dimensions.
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", (SELECT SUM(Revenue) FROM input) AS result FROM input GROUP BY "Region", "Team" ORDER BY "Region", "Team"
Region Team result North Cedar 990 North Pine 990 South Ash 990 South Elm 990
Signature
{ EXCLUDE [Region], [Team] : SUM([Revenue]) }