Calculated field
AVG({ 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, AVG(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, AVG(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, AVG(total) FROM customers GROUP BY Region ORDER BY Region
Written for this dialect; not run here.
Result
| Region | result |
|---|---|
| North | 196.667 |
| South | 200 |
Same rows from the calculation and from the SQL in DuckDB.
Where a naive translation goes wrong
- AVG(Revenue) averages invoices; AVG of INCLUDE customer totals weights each customer once.
Naive SQL
SELECT "Region", AVG(Revenue) AS result FROM input GROUP BY "Region" ORDER BY "Region"
Region result North 147.5 South 100 Matches the calculation
WITH customers AS (SELECT Region, Customer, SUM(Revenue) total FROM input GROUP BY Region, Customer) SELECT Region, AVG(total) FROM customers GROUP BY Region ORDER BY Region
Region result North 196.667 South 200
Signature
AVG({ INCLUDE [Customer] : SUM([Revenue]) })