Calculated field
{ FIXED [Region] : MIN({ FIXED [Customer] : 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
WITH customers AS (SELECT Region, Customer, SUM(Revenue) total FROM input GROUP BY Region, Customer) SELECT Region, MIN(total) FROM customers GROUP BY Region ORDER BY Region
Runs the example above.
WITH customers AS (SELECT Region, Customer, SUM(Revenue) total FROM input GROUP BY Region, Customer) SELECT Region, MIN(total) FROM customers GROUP BY Region ORDER BY Region
Written for this dialect; not run here.
WITH customers AS (SELECT Region, Customer, SUM(Revenue) total FROM input GROUP BY Region, Customer) SELECT Region, MIN(total) FROM customers GROUP BY Region ORDER BY Region
Written for this dialect; not run here.
Result
| Region | result |
|---|---|
| North | 90 |
| South | 100 |
Same rows from the calculation and from the SQL in DuckDB.
Where a naive translation goes wrong
- MIN of individual invoices can miss the smallest customer lifetime total.
Naive SQL
SELECT "Region", MIN(Revenue) AS result FROM input GROUP BY "Region" ORDER BY "Region"
Region result North 80 South 25 Matches the calculation
WITH customers AS (SELECT Region, Customer, SUM(Revenue) total FROM input GROUP BY Region, Customer) SELECT Region, MIN(total) FROM customers GROUP BY Region ORDER BY Region
Region result North 90 South 100
Signature
{ FIXED [Region] : MIN({ FIXED [Customer] : SUM([Revenue]) }) }