How to group output using GROUPING SETS.

Following up with the table from my previous post "How to use the MERGE statement with a subquery", here is an example on how to group the information using the GROUPING SETS functionality in SQL:

The query

SELECT MAJOR,COURSE,TEACHER,SUM(CREDITS) "Total Credits"
FROM  COURSE_CATALOG
GROUP BY GROUPING SETS( (MAJOR,COURSE,TEACHER),(COURSE,TEACHER), (MAJOR,TEACHER),NULL );

The NULL directive tells SQL to generate a grand total at the end of the grouping.
Notice how the GROUPING SETS translates directly into the equivalent of grouping the rows three separate times

First by using

GROUP BY MAJOR, COURSE, TEACHER

then by using

GROUP BY COURSE,TEACHER

and finally by using

GROUP BY MAJOR, TEACHER

MAJOR	COURSE	TEACHER	Total Credits
English Literature	Ex. Phil	A.Peterson	8
Nuclear Science	Physics A	Goodman	8
Nuclear Science	Physics A	A. Brown	8
Nuclear Science	Physics A	R. Franklin	8
Nuclear Science	Mathematics A	M. Stevens	8
English Literature	Greek Mythology	D.Simmons	10
Nuclear Science	Advanced Algebra	K.Wise	10
English Literature	Early British Lit	A.Peterson	8
	Ex. Phil	A.Peterson	8
	Physics A	Goodman	8
	Physics A	A. Brown	8
	Physics A	R. Franklin	8
	Mathematics A	M. Stevens	8
	Greek Mythology	D.Simmons	10
	Advanced Algebra	K.Wise	10
	Early British Lit	A.Peterson	8
Nuclear Science		M. Stevens	8
Nuclear Science		A. Brown	8
English Literature		D.Simmons	10
Nuclear Science		Goodman	8
Nuclear Science		R. Franklin	8
Nuclear Science		K.Wise	10
English Literature		A.Peterson	16
			68