ROLLUP and CUBE.

Let me give you one example where we can use ROLLUP and CUBE.

 I am developing the Employee base application and administrator wants the report which shows Employee Payment. You will say that’s really easy and can write the query as shown below,

create table EMP

( id int not null identity(1,1),

Employeeno varchar(max) NOT NULL,

PAYMENT_DATE varchar(max) NOT NULL,

Amount DECIMAL(7,2) NOT NULL);

INSERT INTO EMP VALUES ( 6, '1980-12-08', 100);
INSERT INTO EMP VALUES ( 44, ' 1981-05-05', 75);
INSERT INTO EMP VALUES ( 104, ' 1984-12-08',50);
INSERT INTO EMP VALUES ( 44, '1984-11-12', 75);
INSERT INTO EMP VALUES ( 8, ' 1980-12-08', 25);
INSERT INTO EMP VALUES ( 44, ' 1980-12-08',25);
INSERT INTO EMP VALUES ( 27, ' 1984-11-12 ', 75);
INSERT INTO EMP VALUES ( 44, ' 1982-12-30', 30);

Fig – (1) Group By clause.
SELECT Employeeno,PAYMENT_DATE,SUM(Amount) as total
FROM EMP GROUP BY Employeeno,PAYMENT_DATE
Which will returns the result as shown Fig – (1)

Here ROLLUP and CUBE comes into the picture and help us. The shown Fig – (2) result is generated using ROLLUP. ROLLUP adds new row for each column used in GROUP BY clause.


CASE WHEN GROUPING(Employeeno)=1 THEN 'TOTAL PAYEE'
ELSE Employeeno END Employeeno,
CASE WHEN GROUPING(PAYMENT_DATE)=1 THEN 'SUB TOTAL'
ELSE PAYMENT_DATE END PAYMENT_DATE,
SUM(Amount)
FROM EMP GROUP BY Employeeno,PAYMENT_DATE WITH ROLLUP

In fig – 2 we have TOTAL PAYEE by employeeno, lets assume you want the TOTAL PAYEE by PAYMENT_DATE also. Here you have to use CUBE as shown below See Result In fig – 3 ,

SELECT
CASE WHEN GROUPING(Employeeno)=1 THEN 'TOTAL PAYEE'
ELSE Employeeno END Employeeno,
CASE WHEN GROUPING(PAYMENT_DATE)=1 THEN 'SUB TOTAL'
ELSE PAYMENT_DATE END PAYMENT_DATE,
SUM(Amount)
FROM EMP GROUP BY Employeeno,PAYMENT_DATE WITH CUBE

Categories: SQL