CODEWITHSUNDEEP
sql-server
Tony
Tony

Reputation: 55

Rollup in SQL is not giving desired output when we use CASE in select Statement

I am using the below query. But when I try to do ROLLUP the column with CASE statement does not show the total in the result for that column. Can anyone help on this? I am trying my hands on.

I am out of options and topics available on net is not helpful. So, the query produces the total for all the columns but not for the column with the CASE statement. I have just provided a rough work not the actual query. 

select   
A,
sum(B),
sum(C),
sum(D),
case when sum(D) = 0  then 0
        else (sum(B)-Sum(C))/Sum(D) as E
from table
group by A with ROLLUP

I am using CASE just because the column D sometimes has 0 as a value and that cannot be used to divide. Any help is appreciated. So the column with CASE is not getting rolled up. Please help. I am stuck on this.

Upvotes: 1

Views: 351

Answers (1)

Avi
Avi

Reputation: 1845

Did you check if you have used end while using case statement, looks like end is missing? From my understanding you would like to add the total generated from the case statement as well. Since E is not a preexisting column, you may need to use sub query to get you expected output.

Create sample Data: 

Select '2019-01-16' as A, 100 as B,         10 as C , 0 as  D 
into #temp union all 
select '2019-01-16' as A, 200 as B,         20 as C , 0 as  D union all 
select '2019-01-17' as A, 300 as B,         30 as C , 10 as D  union all 
select '2019-01-17' as A, 300 as B,         40 as C , 20 as D  union all 
select '2019-01-28' as A, 300 as B,         50 as C , 0 as  D union all 
select '2019-01-28' as A, 400 as B,         60 as C , 50 as D 


select A, 
sum(B) B ,
sum(C) C ,
sum(D) D ,
sum(E) E   from (
select   
A,
sum(B) B ,
sum(C) C ,
sum(D) D ,
case when sum(D) = 0  then 0
else (sum(B)-Sum(C))/Sum(D) end   as E
from #temp
group by A ) tst
group by  A  with ROLLUP`

Output: A B C D E 2019-01-16 300 30 0 0 2019-01-17 600 70 30 17 2019-01-28 700 110 50 11 NULL 1600 210 80 28

Upvotes: 1

Related Questions