![](/style/images/good.png)
![](/style/images/bad.png)
CUBE, ROLLUP, COMPUTE, COMPUTE BY, GROUPING SETS
source link: https://sqlwithmanoj.com/2010/11/12/cube-rollup-compute-compute-by-grouping-sets/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
CUBE, ROLLUP, COMPUTE, COMPUTE BY, GROUPING SETS
The CUBE and ROLLUP operators are useful in generating reports that contain subtotals and totals. There are extensions of the GROUP BY clause.
–> Difference b/w CUBE and ROLLUP:
– CUBE generates a result set that shows aggregates for all combinations of values in the selected columns.
– ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns.
Let’s check this by a simple example:
select
'A'
[class], 1 [rollno],
'a'
[
section
], 80 [marks],
'manoj'
stuName
into
#tempTable
UNION
select
'A'
, 2,
'a'
, 70 ,
'harish'
UNION
select
'A'
, 3,
'a'
, 80 ,
'kanchan'
UNION
select
'A'
, 4,
'b'
, 90 ,
'pooja'
UNION
select
'A'
, 5,
'b'
, 90 ,
'saurabh'
UNION
select
'A'
, 6,
'b'
, 50 ,
'anita'
UNION
select
'B'
, 1,
'a'
, 60 ,
'nitin'
UNION
select
'B'
, 2,
'a'
, 50 ,
'kamar'
UNION
select
'B'
, 3,
'a'
, 80 ,
'dinesh'
UNION
select
'B'
, 4,
'b'
, 90 ,
'paras'
UNION
select
'B'
, 5,
'b'
, 50 ,
'lalit'
UNION
select
'B'
, 6,
'b'
, 70 ,
'hema'
select
class, rollno,
section
, marks, stuName
from
#tempTable
Output: class rollno section marks stuName A 1 a 80 manoj A 2 a 70 harish A 3 a 80 kanchan A 4 b 90 pooja A 5 b 90 saurabh A 6 b 50 anita B 1 a 60 nitin B 2 a 50 kamar B 3 a 80 dinesh B 4 b 90 paras B 5 b 50 lalit B 6 b 70 hema
–> WITH ROLLUP:
select
class,
section
,
sum
(marks) [
sum
]
from
#tempTable
group
by
class,
section
with
ROLLUP
Output: class section sum A a 230 A b 230 A NULL 460 -- 230 + 230 = 460 B a 190 B b 210 B NULL 400 -- 190 + 210 = 400 NULL NULL 860 -- 460 + 400 = 860
–> WITH CUBE:
select
class,
section
,
sum
(marks) [
sum
]
from
#tempTable
group
by
class,
section
with
CUBE
Output: class section sum A a 230 A b 230 A NULL 460 -- 230 + 230 = 460 B a 190 B b 210 B NULL 400 -- 190 + 210 = 400 NULL NULL 860 -- 460 + 400 = 860 NULL a 420 -- 230 + 190 = 420 NULL b 440 -- 230 + 210 = 440
–> COMPUTE & COMPUTE BY: (this feature is no longer supported and discontinued with SQL Server 2012 and next versions)
A COMPUTE BY clause allows you to see both detail and summary rows with one SELECT statement. You can calculate summary values for subgroups, or a summary value for the whole result set.
The COMPUTE clause takes the following information:
– The optional BY keyword. This calculates the specified row aggregate on a per column basis.
– A row aggregate function name. This includes SUM, AVG, MIN, MAX, or COUNT.
– A column upon which to perform the row aggregate function.
select
class,
section
, marks
from
#tempTable
COMPUTE
SUM
(marks),
AVG
(marks)
select
class,
section
, marks
from
#tempTable
order
by
class
COMPUTE
SUM
(marks),
AVG
(marks)
by
class
select
class,
section
, marks
from
#tempTable
order
by
class,
section
COMPUTE
SUM
(marks),
AVG
(marks)
by
class,
section
Final Cleanup, drop the temp tables:
drop
table
#tempTable
–> GROUPING SETS:
SQL Server 2008 has a new GROUPING SETS operator which can generate the same result set as that generated by using a simple GROUP BY, ROLLUP, or CUBE operator.
–> Grouping Sets for SQL Server 2008 and above, check here.
–> Grouping Sets equivalent for SQL Server 2005 and below, check here.
>> Check & Subscribe my [YouTube videos] on SQL Server.
Related
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK