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
Here’s a very nice article by Craig on GROUPING SETS in SQL Server 2008
No comments:
Post a Comment