Calculated field
{ FIXED : 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", (SELECT SUM("Revenue") FROM input) AS result FROM input GROUP BY "Region" ORDER BY "Region"Runs the example above.
SELECT "Region", (SELECT SUM("Revenue") FROM input) AS result FROM input GROUP BY "Region" ORDER BY "Region"Written for this dialect; not run here.
SELECT `Region`, (SELECT SUM(`Revenue`) FROM input) AS result FROM input GROUP BY `Region` ORDER BY `Region`
Written for this dialect; not run here.
Result
| Region | result |
|---|---|
| North | 990 |
| South | 990 |
Same rows from the calculation and from the SQL in DuckDB.
Where a naive translation goes wrong
- Grouping SUM by the visible Region produces regional subtotals instead of the fixed grand total.
Naive SQL
SELECT "Region", SUM("Revenue") AS result FROM input GROUP BY "Region" ORDER BY "Region"Region result North 590 South 400 Matches the calculation
SELECT "Region", (SELECT SUM("Revenue") FROM input) AS result FROM input GROUP BY "Region" ORDER BY "Region"Region result North 990 South 990
Signature
{ FIXED : SUM([Revenue]) }