GROUPING SETS in SQL Server 2008

With the new GROUPING SETS operator included in SQL Server 2008, you have more control over what is aggregated. Here’s a practical example of using GROUPING SETS in SQL Server 2008

SAMPLE DATA

--DROP TABLE #Student

CREATE TABLE #Student
(
StudentID int ,
CourseYear smallint,
Semester smallint,
Marks float
)

INSERT INTO #Student VALUES (1, 2008, 1, 5.6)
INSERT INTO #Student VALUES (1, 2008, 2, 6.5)
INSERT INTO #Student VALUES (1, 2008, 3, 8.9)
INSERT INTO #Student VALUES (1, 2008, 4, 9.1)
INSERT INTO #Student VALUES (1, 2009, 1, 4.4)
INSERT INTO #Student VALUES (1, 2009, 2, 7.9)
INSERT INTO #Student VALUES (1, 2009, 3, 8.5)
INSERT INTO #Student VALUES (1, 2009, 4, 8.7)
INSERT INTO #Student VALUES (2, 2008, 1, 5.4)
INSERT INTO #Student VALUES (2, 2008, 2, 9.9)
INSERT INTO #Student VALUES (2, 2008, 3, 8.5)
INSERT INTO #Student VALUES (2, 2008, 4, 4.7)
INSERT INTO #Student VALUES (2, 2009, 1, 6.4)
INSERT INTO #Student VALUES (2, 2009, 2, 7.9)
INSERT INTO #Student VALUES (2, 2009, 3, 7.4)
INSERT INTO #Student VALUES (2, 2009, 4, 9.7)

Let’s do some common GROUPBY operations

-- Calculate Total Marks obtained by student each year
SELECT StudentID, CourseYear, SUM(Marks) AS TotalMarks
FROM #Student
GROUP BY StudentID, CourseYear
ORDER BY StudentID

-- Calculate Total Marks obtained by student
-- grouped by Semester for both years
SELECT StudentID, Semester, SUM(Marks) AS TotalMarks
FROM #Student
GROUP BY StudentID, Semester
ORDER BY StudentID

However with the GROUPING SETS operator, you can define different aggregate groups in a single query as shown below

SELECT StudentID, CourseYear, Semester, SUM(Marks) AS TotMarks
FROM #Student
GROUP BY GROUPING SETS((StudentID, CourseYear), (StudentID, Semester))
ORDER BY StudentID, Semester