SQL 'incorrect' calculation of a column

Question

I have a calculated column and it doesn't give me the same result as the manual calculation using the other columns.

SELECT  VG.avg_tax, 
        VP2.prop * (S.points / 100) / (NULLIF(CAST(VG.avg_tax AS DECIMAL(7 , 2)) , 0)) AS net_points,
        VP2.prop ,
        S.points,
        (NULLIF(CAST(VG.avg_tax AS DECIMAL(7 , 2)) , 0)) AS divisio ,
        VP2.new_tax

This is the code first part of the code, where I believe the error to be.

The results are:

+---------+------------+------+--------+----------+----------+
| avg_tax | net_points | prop | points |  divisio |  new_tax |
+---------+------------+------+--------+----------+----------+
|    1.23 |  43.902439 |    1 |   5442 |     1.23 |       23 |
+---------+------------+------+--------+----------+----------+

As you can see, by calculating by hand, the result at net_points should be: 5442/100 * 1 / 1.23 = 44.2439.

Why is this happening?

score 0 · Answer 1 · answered Jun 30 '21 at 14:16

0

I don't understand exactly why it happens, but S.points being an integer changes the result of S.points / 100.

Substituting S.points by CAST(S.points AS DECIMAL(7 , 2)) makes the results correct.

answered Jun 30 '21 at 14:16

Miquel Plens

13
2

score 0 · Answer 2 · answered Jun 30 '21 at 15:25

You don't specify the database or the data types, but some databases do integer division. As a habit, I usually include decimal points in constants just to be sure:

    VP2.prop * (S.points / 100.0) / (NULLIF(CAST(VG.avg_tax AS DECIMAL(7 , 2)) , 0)) AS net_points,

SQL 'incorrect' calculation of a column

2 Answers2