CUBE,COMPUTE,COMPUTE BY and ROLLUP OPERATOR IN SQL SERVER

The CUBE,COMPUTE,COMPUTE BY and ROLLUP operators are useful in generating reports that contain subtotals and totals.

There are extensions of the GROUP BY clause.

The result set of a ROLLUP operation has functionality similar to that returned by a COMPUTE BY; however, ROLLUP has these advantages:
ROLLUP returns a single result set; COMPUTE BY returns multiple result sets that increase the complexity of application code.

ROLLUP can be used in a server cursor; COMPUTE BY cannot.

The query optimizer can sometimes generate more efficient execution plans for ROLLUP than it can for COMPUTE BY.

STEP 1:

create table JSSTUD(COL nvarchar(20),PER numeric(10))

STEP 2:

INSERT INTO JSSTUD VALUES('JJ COLLEGE',100)
INSERT INTO JSSTUD VALUES('JJ COLLEGE',200)
INSERT INTO JSSTUD VALUES('JJ COLLEGE',300)
INSERT INTO JSSTUD VALUES('PSG',150)
INSERT INTO JSSTUD VALUES('PSG',50)
INSERT INTO JSSTUD VALUES('PSG',1050)
INSERT INTO JSSTUD VALUES('GRG',2000)

STEP 3:

ROLLUP
SELECT COL,SUM(PER) FROM JSSTUD GROUP BY COL
WITH ROLLUP


OR
COMPUTE bY

SELECT COL,PER FROM JSSTUD ORDER BY COL
COMPUTE SUM(PER) BY COL



OR

SELECT COL,PER FROM JSSTUD ORDER BY COLCOMPUTE SUM(PER)

CUBE

select COL, sum(per)  from JSSTUD group by COL with CUBE

0 Comments

Featured Post

Understanding about Azure Region, Geographies, Availability Zones and Region Pairs

Introduction Microsoft Azure services are available in 53 regions around the world, with plans announced for 7 additional regions and 140+ a...

Subscribe to the FREE Weekly Newsletter to receive all the latest Microsoft Azure, AI, Xamarin

Popular Posts