In SQL Server, the ROLLUP clause is used to generate subtotal and grand total values for a set of data. The ROLLUP clause is used in conjunction with the GROUP BY clause, and it generates additional rows that contain subtotal and grand total values for the data that is being aggregated.

Syntax

The syntax for the ROLLUP clause is as follows:

SELECT column1, column2, ..., columnN, aggregate_function(columnX)
FROM table_name
GROUP BY column1, column2, ..., columnN WITH ROLLUP;

In this syntax, the aggregate_function is the function that is used to aggregate the data in columnX. The columns in the SELECT statement represent the grouping columns, which are used to group the data for the aggregation. The WITH ROLLUP option is used to indicate that subtotal and grand total rows should be generated.

The ROLLUP clause generates a result set that contains the original rows, as well as additional rows that contain subtotal and grand total values. The subtotal rows contain aggregated values for each level of grouping, and the grand total row contains aggregated values for all levels of grouping.

Example

For example, consider the following table:

To generate subtotal and grand total values for this table, we can use the following query:

SELECT Region, City, SUM(Sales)
FROM Sales
GROUP BY Region, City WITH ROLLUP;

This query generates the following result set:

In this result set, the subtotal rows contain aggregated values for each level of grouping (i.e., by region and city), and the grand total row contains aggregated values for all levels of grouping (i.e., the entire table). The NULL values in the Region and City columns represent the subtotal and grand total rows, respectively.