Advertisements

Archive

Posts Tagged ‘ROLLUP’

GROUPING SETS equivalent | for SQL Server 2005 and below

March 23, 2015 4 comments

Sometime back I discussed about [GROUPING SETS] in my previous post and today one reader of this blog asked me how we can create the same result-set without using GROUPING SETS if we are on previous versions like SQL Server 2005 or 2000.
 

–> Let’s take the following SQL Query that uses GROUPING SETS operator and let’s see what Output we get:

-- GROUPING SETS
SELECT class, section, rollno, sum(marks) [sum]
FROM #tempTable
GROUP BY GROUPING SETS (
	 (class, section, rollno)
	,(class, section)
	,(class)
	,()
)
Output:
class		section	rollno	sum
HighSchool	a	1	80
HighSchool	a	2	70
HighSchool	a	3	80
HighSchool	a	NULL	230
HighSchool	b	4	90
HighSchool	b	5	90
HighSchool	b	6	50
HighSchool	b	NULL	230
HighSchool	NULL	NULL	460
Intermediate	a	1	60
Intermediate	a	2	50
Intermediate	a	3	80
Intermediate	a	NULL	190
Intermediate	b	4	90
Intermediate	b	5	50
Intermediate	b	6	70
Intermediate	b	NULL	210
Intermediate	NULL	NULL	400
NULL		NULL	NULL	860

You can refer to the DDL and population of this #tempTable in my previous post, [link].
 

–> And now let’s see how can we recreate the same result set without using GROUPING SETS if we are on lower versions of SQL Server (<= 2005):

-- ROLLUP and Grouping Sets Equivalent (pre SQL 2008)
SELECT class, section, rollno, marks as [sum]
FROM #tempTable

UNION ALL

SELECT class, section, null as rollnu, sum(marks) [sum]
FROM #tempTable
GROUP BY class, section

UNION ALL

SELECT class, null, null, sum(marks) [sum]
FROM #tempTable
GROUP BY class

UNION ALL

SELECT null, null, null, sum(marks) [sum]
FROM #tempTable

ORDER BY class DESC, section DESC, rollno DESC

 

Thanks, please let me know your thoughts and comments!!!


Advertisements
Categories: SQL Tips Tags: , , ,

Using GROUPING SETS | SQL Server 2008 and above

November 20, 2014 2 comments

In my previous posts (long back) I discussed about using [CUBE and ROLLUP] operators introduced in SQL Server 2008. I also discussed about COMPUTE & COMPUTE BY operators, but there two were removed from the SQL Server 2012 version.

Here in this post I’ll talk about GROUPING SETS (few days back my friend asked me about this and I thought its better to document this here).

Just like CUBE and ROLLUP, GROUPING SETS are used to GROUP and calculate Sub Totals and Totals within a set of records. Compared to ROLLUP with GROUPING SETS you have more flexibility to show Sub Totals on selected columns.

-- ROLLUP
SELECT class, section, rollno, sum(marks) [sum]
FROM #tempTable
GROUP BY class, section, rollno WITH ROLLUP
Output:
class		section	rollno	sum
HighSchool	a	1	80
HighSchool	a	2	70
HighSchool	a	3	80
HighSchool	a	NULL	230
HighSchool	b	4	90
HighSchool	b	5	90
HighSchool	b	6	50
HighSchool	b	NULL	230
HighSchool	NULL	NULL	460
Intermediate	a	1	60
Intermediate	a	2	50
Intermediate	a	3	80
Intermediate	a	NULL	190
Intermediate	b	4	90
Intermediate	b	5	50
Intermediate	b	6	70
Intermediate	b	NULL	210
Intermediate	NULL	NULL	400
NULL		NULL	NULL	860

 

–> Now, if you want to just Sub Total on Class level, you just need to use GROUPING SET operator like this:

-- GROUPING SETS on selective columns
SELECT class, section, rollno, sum(marks) [sum]
FROM #tempTable
GROUP BY GROUPING SETS (
	 (class, section, rollno)
	,(class)
)

This will give you more controlled Sub Totaling and grouping on selected columns:

Output:
class		section	rollno	sum
HighSchool	a	1	80
HighSchool	a	2	70
HighSchool	a	3	80
HighSchool	b	4	90
HighSchool	b	5	90
HighSchool	b	6	50
HighSchool	NULL	NULL	460
Intermediate	a	1	60
Intermediate	a	2	50
Intermediate	a	3	80
Intermediate	b	4	90
Intermediate	b	5	50
Intermediate	b	6	70
Intermediate	NULL	NULL	400

 

–> You can also get the same output like ROLLUP operator, but you have to provide columns in following groups:

-- GROUPING SETS
SELECT class, section, rollno, sum(marks) [sum]
FROM #tempTable
GROUP BY GROUPING SETS (
	 (class, section, rollno)
	,(class, section)
	,(class)
	,()
)

Note: You can also use GROUPING SETS with combination of ROLUP and/or CUBE operators, check more on regarding this on MS BoL, here.


 

–> Grouping Sets equivalent for SQL Server 2005 and below, check here.

CUBE, ROLLUP, COMPUTE, COMPUTE BY, GROUPING SETS

November 12, 2010 10 comments

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.