Newbie mystery

Try running the code below in QA

USE Northwind
--The quesy below produces the correct numbers.
SELECT CategoryID,(100*((COUNT(*)+.0)/(SELECT COUNT(*) AS TotalCount FROM Products))) AS PERCENT_CAT FROM Products GROUP BY CategoryID

--The query below produces 0 values and are wrong.
SELECT CategoryID,(100*((COUNT(*))/(SELECT COUNT(*) AS TotalCount FROM Products))) AS PERCENT_CAT FROM Products GROUP BY CategoryID

--This is the total
SELECT COUNT(*) AS TotalCount FROM Products
--The totals of the groupings
SELECT CategoryID,(COUNT(*)) AS Category_Total FROM Products GROUP BY CategoryID

I think I understand what happens with the above, but what I really want to know is there a good coding habit to prevent it. Some of our reports are very complex and an error could be missed.Dear,

In MSSQL, this is normal behaviour. When you divide an integer (count) by another integer (count), the result is an integer.

Example :

select 1/3 returns 0

select 1/cast(3 as numeric) returns .33333

That is just the way it is, and it is documented in BOL.

Regards,

CVM.