Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 462
vision Avatar asked Sep 19 '13 14:09

vision


People also ask

How do you specify an alias in SQL?

The basic syntax of a table alias is as follows. SELECT column1, column2.... FROM table_name AS alias_name WHERE [condition];

What is alias in SQL with example?

SQL aliases are used to give a table, or a column in a table, a temporary name. Aliases are often used to make column names more readable. An alias only exists for the duration of that query. An alias is created with the AS keyword.

Which clause is used for rolling up data?

Introduction to SQL ROLLUP The ROLLUP is an extension of the GROUP BY clause. The ROLLUP option allows you to include extra rows that represent the subtotals, which are commonly referred to as super-aggregate rows, along with the grand total row.


2 Answers

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

like image 154
Martin Smith Avatar answered Oct 04 '22 08:10

Martin Smith


select 
isnull([column1],'rollup'), 
sum([column2] )
from tableA
group by [column1] 
WITH ROLLUP
like image 32
Roman Sergeev Avatar answered Oct 04 '22 10:10

Roman Sergeev