Calculating Percentage of Row over Total Sum

Question

Hello everyone and thanks for the help in advance. I am trying to develop a SQL query that returns the total number of patient encounters by insurance company that also calculates the percentage of the total for each row returned. The query works until the calculations become very small (less that 5%) where it returns only 0. Here is my query:

	Select Distinct Insurance, Count(*) as Count, cast(100 * Count(*) / (SUM(Count(*)) OVER ()) AS decimal(18,2))as Percentage from tblPatientVisits where CheckedOut Between @BeginningDate and DATEADD(day, 1, @EndingDate) 
		Group By Insurance Order By Count Desc

and here is the data returned.

(No column name) Count Percentage

Insurance 1377 52.00

Insurance 586 22.00

Insurance 401 15.00

Insurance 138 5.00

Insurance 80 3.00

Insurance 14 0.00

Insurance 7 0.00

Insurance 2 0.00

Insurance 1 0.00

The company names are redacted, however, the counts for each row are correct. However, by row 6, the percentage returns 0 and I can't figure out why. Any help would be appreciated.

Answer 1

You need to keep mind that the result of the / operator depends on the input types. If you feed integer values, it perform integer division. Check this out:

SELECT 1/3, 1.0/3.0, 1E0/3E0

This returns

0    0.333333  0,333333333333333

Instead of 100, put 1E2. Because float has higher precedence than int, which will result in remaining factors to be implicitly converted to float.