SQL Server Rounding Issue Looking for Explanation

Question

Hi Everyone,

I've solved this issue but I'm just wondering why this works the way it does. I have a temporary table I am selecting from and am looking to display a a name, the number of records that match this name, and the percentage for that name of the total records. This is the way I originally had it:

SELECT name, number,  
CASE WHEN number = 0 THEN 0 ELSE
convert(Numeric(10,2), number / CONVERT(decimal(5,2),SUM(number)) * 100)
END as "Percentage of Total" 
FROM #names 
group by name, number

The results I received were:

name                      number      Percentage of Total
------------------------- ----------- ---------------------------------------
Test 1                      0           0.00
Test 2                     22          100.00
Test 3                     28          100.00

When I change the query to this, the results are correct:

    declare @total decimal(5,2)

    select @total = SUM(number) FROM #names

    SELECT name, number, convert(Numeric(10,2), number/ @total * 100) as "Percentage of Total"  
    FROM #names
    group by name, number

Correct Results:

name                      number      Percentage of Total
------------------------- ----------- ---------------------------------------
Test 1                     22          44.00
Test 2                      0           0.00
Test 3                     28          56.00

Can someone explain what is going on, I would like to understand this better. Thanks!

Jon

Answer 1

You first query groups by number.

Since you don't have duplicates of numbers, number / SUM(number) is equivalent to the 1 / COUNT (except when the number is 0).

You second query does not group by number, it calculates total sum.

Use this instead:

SELECT  name, number * 100.0 / SUM(number) OVER ()
FROM    #names

When used with OVER clause, SUM becomes the analytical function rather than the aggregate one.

It does not shrink several records into one: instead, it returns the total value along with each record:

-- This is an aggregate function. It shrinks all records into one record and returns the total sum

WITH    q (name, number) AS
        (
        SELECT  'test1', 0
        UNION ALL
        SELECT  'test2', 22
        UNION ALL
        SELECT  'test3', 28
        )
SELECT  SUM(number)
FROM    q

--
50

---

-- This is an analytical function. It calcuates the total sum as well but does not shrink the records.

WITH    q (name, number) AS
        (
        SELECT  'test1', 0
        UNION ALL
        SELECT  'test2', 22
        UNION ALL
        SELECT  'test3', 28
        )
SELECT  SUM(number) OVER ()
FROM    q

--
50
50
50