Calculated field
AVG({ INCLUDE [Team] : 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 teams AS (SELECT Region, Team, SUM(Revenue) total FROM input GROUP BY Region, Team) SELECT Region, AVG(total) FROM teams GROUP BY Region ORDER BY Region
Runs the example above.
WITH teams AS (SELECT Region, Team, SUM(Revenue) total FROM input GROUP BY Region, Team) SELECT Region, AVG(total) FROM teams GROUP BY Region ORDER BY Region
Written for this dialect; not run here.
WITH teams AS (SELECT Region, Team, SUM(Revenue) total FROM input GROUP BY Region, Team) SELECT Region, AVG(total) FROM teams GROUP BY Region ORDER BY Region
Written for this dialect; not run here.
Result
| Region | result |
|---|---|
| North | 295 |
| South | 200 |
Same rows from the calculation and from the SQL in DuckDB.
Where a naive translation goes wrong
- AVG of invoices differs from the average of whole team totals.
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 teams AS (SELECT Region, Team, SUM(Revenue) total FROM input GROUP BY Region, Team) SELECT Region, AVG(total) FROM teams GROUP BY Region ORDER BY Region
Region result North 295 South 200
Signature
AVG({ INCLUDE [Team] : SUM([Revenue]) })