Excellent! As you saw, we used
ROLLUP() in the
GROUP BY clause:
DATEPART(Year, OrderDate) AS RevenueYear,
DATEPART(Quarter, OrderDate) AS RevenueQuarter,
SUM(Amount) AS TotalRevenue
GROUP BY ROLLUP(
In SQL Server,
ROLLUP() is an extension of
GROUP BY. Inside the brackets, we provide all the columns that we want to group the rows by.
ROLLUP() first groups the rows by both columns (in this case,
Quarter) to compute quarterly sums. Then, it groups all rows by the first column inside the parentheses (
Year) to compute the annual sums. Finally, it computes a grand total, i.e. as if we didn't group the rows by anything.
As a general rule,
ROLLUP() always creates new grouping combinations by removing columns one by one, starting from the right:
GROUP BY ROLLUP (A, B, C) =
GROUP BY (A, B, C) +
GROUP BY (A, B) +
GROUP BY (A) +
GROUP BY ()
The diagram below explains where the resulting rows come from:
In this course, we're only going to briefly introduce
ROLLUP(). To learn more about this and other
GROUP BY extensions, check out our GROUP BY Extensions in MS SQL Server course.