CODEWITHSUNDEEP
sqlsql-servert-sql
jprice92
jprice92

Reputation: 415

ROLLUP line not summing correctly when there is a CASE statement

I'm trying to write a report that I have working 99% of the way except for a single column that's not summing the rollup correctly. I believe I've replicated a simple version of it below:

CODE

DECLARE @table TABLE (
 id VARCHAR(10)
,number1 MONEY
,number2 MONEY
)

INSERT INTO @table (
 id
,number1
,number2
) VALUES (
 1
,10
,200
)

INSERT INTO @table (
 id
,number1
,number2
) VALUES (
 2
,100    
,20
)

SELECT
CASE WHEN GROUPING(id) = 1
    THEN 'Total'
    ELSE id
END as ID
,CASE 
    WHEN SUM(number1) > SUM(number2)
     THEN SUM(number1)
     ELSE SUM(number2)
    END as GreaterNum
,GROUPING(id) as idgroup
FROM @table
GROUP BY id WITH ROLLUP

My expected results would be the TOTAL line would SUM the two greater nums (100 + 200) and therefore = 300

EXEPECTED RESULT

ID   | GreaterNum   | IDGroup
 1         200           0
 2         100           0
TOTAL      300           1

However what it is doing is summing only number2 for the TOTAL rollup line

ACTUAL RESULT

ID   | GreaterNum   | IDGroup
 1         200           0
 2         100           0
TOTAL      220           1

I imagine what I have to to is create another temp table beforehand and do the case statement there and join it to get my desired result but I'm wondering if there's a more simple answer.

Upvotes: 2

Views: 147

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

I would use APPLY :

SELECT ISNULL(id, 'Total') Id, SUM(GreaterNum) GreaterNum   
FROM @table t CROSS APPLY
     ( VALUES (CASE WHEN number1 > number2 THEN number1 ELSE number2 
               END) 
     ) tt(GreaterNum)
GROUP BY id WITH ROLLUP;

However, i just declined GROUPING() which is actually not necessary to display Total.

Upvotes: 2

Related Questions