Calculated field
ATTR([amount])
Sample data
| id | amount | units | score | other |
|---|---|---|---|---|
| 1 | 4 | 2 | -0.8 | 3 |
| 2 | 4 | 0 | 0 | 4 |
| 3 | 4 | 3 | 0.25 | 2 |
| 4 | 4 | 4 | 0.5 | 8 |
| 5 | 4 | 5 | 0.8 | 11 |
| 6 | 4 | 6 | 1 | 14 |
| 7 | null | 7 | null | 9 |
| 8 | 4 | 8 | -0.5 | null |
The same calculation as SQL
SELECT CASE WHEN count(DISTINCT amount)=0 THEN NULL WHEN count(DISTINCT amount)=1 THEN min(amount) ELSE NULL END AS result FROM input
Runs the example above.
SELECT CASE WHEN count(DISTINCT amount)=0 THEN NULL WHEN count(DISTINCT amount)=1 THEN min(amount) ELSE NULL END AS result FROM input
Written for this dialect; not run here.
SELECT CASE WHEN count(DISTINCT amount)=0 THEN NULL WHEN count(DISTINCT amount)=1 THEN min(amount) ELSE NULL END AS result FROM input
Written for this dialect; not run here.
Result
| result |
|---|
| 4 |
Same rows from the calculation and from the SQL in DuckDB.
Where a naive translation goes wrong
- MIN silently selects a value when ATTR detects multiple distinct values.
Naive SQL
SELECT min(amount) AS result FROM input
result -2.5 Matches the calculation
SELECT CASE WHEN count(DISTINCT amount)=0 THEN NULL WHEN count(DISTINCT amount)=1 THEN cast(min(amount) AS VARCHAR) ELSE '*' END AS result FROM input
result *
Signature
ATTR(expression)