DIV in Tableau: the SQL equivalent, with a worked example

DIV([units], 3)

id	amount	units	score	other
1	-2.5	2	-0.8	3
2	0	0	0	4
3	1.5	3	0.25	2
4	4	4	0.5	8
5	9	5	0.8	11
6	16	6	1	14
7	null	7	null	9
8	4	8	-0.5	null

SELECT trunc(units/3) AS result FROM input ORDER BY id

Runs the example above.

SELECT trunc(units/3) AS result FROM input ORDER BY id

Written for this dialect; not run here.

SELECT div(units,3) AS result FROM input ORDER BY id

Written for this dialect; not run here.

id	amount	units	score	result
1	-2.5	2	-0.8	0
2	0	0	0	0
3	1.5	3	0.25	1
4	4	4	0.5	1
5	9	5	0.8	1
6	16	6	1	2
7	null	7	null	2
8	4	8	-0.5	2

Same rows from the calculation and from the SQL in DuckDB.

Where a naive translation goes wrong

Floating-point division retains a fractional quotient.
Naive SQL
```
SELECT units/3 AS result FROM input ORDER BY id
```
id amount units score result
1 -2.5 2 -0.8 0.666667
2 0 0 0 0
3 1.5 3 0.25 1
4 4 4 0.5 1.33333
5 9 5 0.8 1.66667
6 16 6 1 2
7 null 7 null 2.33333
8 4 8 -0.5 2.66667
Matches the calculation
```
SELECT trunc(units/3) AS result FROM input ORDER BY id
```
id amount units score result
1 -2.5 2 -0.8 0
2 0 0 0 0
3 1.5 3 0.25 1
4 4 4 0.5 1
5 9 5 0.8 1
6 16 6 1 2
7 null 7 null 2
8 4 8 -0.5 2

id	amount	units	score	result
1	-2.5	2	-0.8	0.666667
2	0	0	0	0
3	1.5	3	0.25	1
4	4	4	0.5	1.33333
5	9	5	0.8	1.66667
6	16	6	1	2
7	null	7	null	2.33333
8	4	8	-0.5	2.66667

DIV(integer, integer)