Calculated field
{ FIXED [Customer] : COUNT([Revenue]) }
Sample data
| Region | Team | Customer | Revenue | Seats | Date |
|---|---|---|---|---|---|
| North | Cedar | Aster | 120 | 4 | 2026-01-05 |
| North | Cedar | Aster | 120 | 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 "Customer", COUNT(Revenue) AS result FROM input GROUP BY "Customer" ORDER BY "Customer"
Runs the example above.
SELECT "Customer", COUNT(Revenue) AS result FROM input GROUP BY "Customer" ORDER BY "Customer"
Written for this dialect; not run here.
SELECT `Customer`, COUNT(Revenue) AS result FROM input GROUP BY `Customer` ORDER BY `Customer`
Written for this dialect; not run here.
Result
| Customer | result |
|---|---|
| Aster | 2 |
| Birch | 1 |
| Clover | 1 |
| Dahlia | 2 |
| Fern | 2 |
Same rows from the calculation and from the SQL in DuckDB.
Where a naive translation goes wrong
- COUNT(DISTINCT Revenue) collapses separate invoices with the same amount; COUNT keeps both.
Naive SQL
SELECT "Customer", COUNT(DISTINCT Revenue) AS result FROM input GROUP BY "Customer" ORDER BY "Customer"
Customer result Aster 1 Birch 1 Clover 1 Dahlia 2 Fern 2 Matches the calculation
SELECT "Customer", COUNT(Revenue) AS result FROM input GROUP BY "Customer" ORDER BY "Customer"
Customer result Aster 2 Birch 1 Clover 1 Dahlia 2 Fern 2
Signature
{ FIXED [Customer] : COUNT([Revenue]) }