Alias names to with rollup in SQL queries?

Question

I am using with rollup in my sql query. I am not getting alias name for rollup.

My SQL is

SELECT [Column1],
       sum([Column2])
FROM   Tablea
GROUP  BY [Column2] WITH ROLLUP

Which returns

How can I replace the NULL in the total row?

score 26 · Answer 1 · answered Sep 19 '13 at 14:31

26

You can use the GROUPING function in a CASE expression.

SELECT CASE
         WHEN GROUPING([Column1]) = 1 THEN 'Total'
         ELSE [Column1]
       END [Column1],
       sum([Column2])
FROM   Tablea
GROUP  BY [Column1] WITH ROLLUP

SQL Fiddle

answered Sep 19 '13 at 14:31

Martin Smith

438,706
87
741
845

3

@Ethan: Questions should be asked in **Questions** rather than Comments; so they can be answered in **Answers** instead of additional Comments. – Pieter Geerkens Dec 28 '14 at 21:39

score 7 · Answer 2 · answered Sep 19 '13 at 14:34

7

select 
isnull([column1],'rollup'), 
sum([column2] )
from tableA
group by [column1] 
WITH ROLLUP

answered Sep 19 '13 at 14:34

Roman Sergeev

196
5

4

This does not return the correct results if `column1` contains `NULL`s. The `GROUPING` function is the correct way. ["GROUPING is used to distinguish the null values that are returned by ROLLUP, CUBE or GROUPING SETS from standard null values."](http://technet.microsoft.com/en-us/library/ms178544.aspx) – Martin Smith Oct 20 '13 at 16:31

score -4 · Answer 3 · edited Feb 13 '17 at 08:05

-4

SELECT ifnull([column1],'total'),
       sum([Column2])
FROM   Tablea
GROUP  BY [Column2] WITH ROLLUP

edited Feb 13 '17 at 08:05

FelixSFD

6,052
10
43
117

answered Feb 13 '17 at 08:03

Anurag Dwivedi

1

Alias names to with rollup in SQL queries?

3 Answers3

Linked

Related