Tuesday, February 22, 2011

Dividing count(*) in SQL Server results in 0

Example: SELECT (COUNT(*) / (SELECT COUNT(*) FROM TABLE)) * 100 as [Percentage] FROM TABLE WHERE Val=10
This always returns 0. Why? It's because when SQL Server divides 2 integers, it returns an integer.

Solution:
Convert one of the dividing numbers to float. For example, using the above query:
SELECT (CONVERT(float, COUNT(*)) / (SELECT COUNT(*) FROM TABLE)) * 100 as [Percentage] FROM TABLE WHERE Val=10

Reference

No comments:

Post a Comment

There was an error in this gadget