Alias names to with rollup in SQL queries?

问题:

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

s       8
t       8
j       8
null    24 

How can I replace the NULL in the total row?

回答1:

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

回答2:

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

回答3:

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