ROLLUP Grouping set in SSMS

Roll up aggregates selected column value for a hierarchy of values along with subtotal and grand total.

For better understanding will go for a example.

Suppose we are having a student marks record of a school in a table named Marks_Record .

select Class,Roll_No,Name,Section,Scored_marks from Marks_Record

Now ,if we do Roll up operation based on 'Section' column of this table then will form the column like below result set Model.

Hope now we get a clear picture of this concept, will see the SQL query for this. 

SELECT CLASS,SECTION,SUM(SCORED_MARKS) AS [TOTAL]

FROM Marks_Record

GROUP BY CLASS, SECTION WITH ROLLUP

Output:

Instead of NULL value we can do some decoration to this result.

SELECT ISNULL(CLASS,'ALL') AS CLASS ,

CASE WHEN CLASS IS NULL THEN ISNULL(SECTION,'GRAND TOTAL')ELSE ISNULL(SECTION,'SUB TOTAL') END AS SECTION ,

SUM(SCORED_MARKS) AS [TOTAL]

FROM Marks_Record

GROUP BY CLASS, SECTION WITH ROLLUP

Output: