Calculated field
SUM([Revenue]) / { 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", SUM(Revenue)*1.0/(SELECT SUM(Revenue) FROM input) AS result FROM input GROUP BY "Region" ORDER BY "Region"
Runs the example above.
SELECT "Region", SUM(Revenue)*1.0/(SELECT SUM(Revenue) FROM input) AS result FROM input GROUP BY "Region" ORDER BY "Region"
Written for this dialect; not run here.
SELECT `Region`, SUM(Revenue)*1.0/(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 | 0.59596 |
| South | 0.40404 |
Same rows from the calculation and from the SQL in DuckDB.
Where a naive translation goes wrong
- Dividing by the current Region SUM produces one in every region instead of its global share.
Naive SQL
SELECT "Region", SUM(Revenue)*1.0/SUM(Revenue) AS result FROM input GROUP BY "Region" ORDER BY "Region"
Region result North 1 South 1 Matches the calculation
SELECT "Region", SUM(Revenue)*1.0/(SELECT SUM(Revenue) FROM input) AS result FROM input GROUP BY "Region" ORDER BY "Region"
Region result North 0.59596 South 0.40404
Signature
SUM([Revenue]) / { FIXED : SUM([Revenue]) }