SQL Incorrect Percentage Calculations

user_18153430 0
Viewed 64

I'm currently writing up a small report and I have to create figures for target percentages however the query I have written seems to display incorrect calculations for percentages and I'm not entirely sure why. The query has removed all sensitive information;

SELECT  YearMonth
, Code
, COUNT(*) AS Total
, (COUNT(*) / 100 * 2) AS 'Q1 Target'
, (COUNT(*) / 100 * 3) AS 'Q2 Target'
, (COUNT(*) / 100 * 4) AS 'Q3 Target'
, (COUNT(*) / 100 * 5) AS 'Q4 Target'
FROM xxx
LEFT JOIN Calendar AS C ON Date = xxx
WHERE code IN (xxx)
and xxx BETWEEN '20220301' AND '20220331'
GROUP BY YearMonth, code

The results that are returned are;

As you can see, the first row has a total of 611 and 2% of that is 12 which is correct. However, if you look at the 3rd row, you can see that the total is 659 and 2% of that should be 13 but it is showing as 12. The same applies on the next column where 3% of 659 should be 19 but is showing as 18. Only whole integers are required so no need to cast.

Any reasons as to why this is happening?

tsql sql-server sql
1 Answers

Only whole integers are required so no need to cast.

Are you sure?

659 / 100 in integer division results in 6 and throws the remainder away. 6*2=12.

Change your 100s to 100.0s to force float decimal division. You might need some rounding/text functions to return to the pleasing two digits afterwards.

user_58259630
Related