The ROLLUP clause in SQL Server is an extension of the grouping set operator. This article will give a complete overview of the ROLLUP clause to aggregates different groups with subtotals and grand totals.
It is the subclass of the GROUP BY clause that performs an aggregate operation on multiple levels in a hierarchy and prepares summary reports. It allows us to generate multiple grouping sets within a single query, which is impossible with the GROUP BY clause as it aggregates a single group. Thus, we can say that the ROLLUP provides a more detailed analysis by employing a single query to create several grouping sets along the hierarchy of columns.
Syntax:
SELECT column_lists, aggregate_function (column_name) FROM table_name GROUP BY ROLLUP (column_names);
Example Code:
select year(InvoiceDate) as c_year , MONTH(InvoiceDate) as c_month , count(1) as c_count
from smart1.Invoices
group by year(InvoiceDate) , rollup (month(InvoiceDate))
This query will return month wise total no of sales and summary
