Calculated field
MEDIAN({ INCLUDE [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, MEDIAN(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, MEDIAN(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 DISTINCT Region, PERCENTILE_CONT(total, 0.5) OVER (PARTITION BY Region) AS result FROM customers ORDER BY Region
Written for this dialect; not run here.
Result
| Region | result |
|---|---|
| North | 200 |
| South | 200 |
Same rows from the calculation and from the SQL in DuckDB.
Where a naive translation goes wrong
- MEDIAN of invoices differs from the median of summed customer totals.
Naive SQL
SELECT "Region", MEDIAN(Revenue) AS result FROM input GROUP BY "Region" ORDER BY "Region"
Region result North 105 South 87.5 Matches the calculation
WITH customers AS (SELECT Region, Customer, SUM(Revenue) total FROM input GROUP BY Region, Customer) SELECT Region, MEDIAN(total) FROM customers GROUP BY Region ORDER BY Region
Region result North 200 South 200
Signature
MEDIAN({ INCLUDE [Customer] : SUM([Revenue]) })