SQL Server ROLLUP

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.

NOTE: ROLLUP modifier produces the summary output, including extra rows that represent super-aggregate summary operations. It is mainly used to provide support for OLAP (Online Analytical Processing) operations.

Syntax

The following is the basic syntax that illustrates the ROLLUP clause in SQL Server:

  SELECT column_lists,  aggregate_function (column_name)  FROM table_name  GROUP BY ROLLUP (column_names);  

The parameters of the above syntax are described below:

column_lists: It indicates the name of columns from the defined table.
aggregate_function (column): It is used to perform aggregation on given columns such as SUM, COUNT, AVG, etc.
table_name: It indicates the source table name from which data will be fetched.
GROUP BY: It’s a clause used to specify a single column or multiple columns to create a group on which the aggregate operation is performed.
ROLLUP: It is used with the combination of the GROUP BY clause for creating multiple groups (i.e., grouping set) and hierarchically applies the aggregate function.

SQL Server also provides another syntax to use the ROLLUP clause as given below:

  SELECT column_lists,  aggregate_function (column_name)  FROM table_name  GROUP BY column_list WITH ROLLUP;     

Both the syntax will return the same output.

SQL Server ROLLUP Example

Let us understand when and how the ROLLUP clause is used in the query practically. We can do this by first creating a table named EMPLOYEE using the below statement:

  CREATE TABLE EMPLOYEE (   emp_id int identity,   fullname varchar(65),   occupation varchar(45),   gender varchar(30),   salary int,   country varchar(55)  );  

Next, we will insert some records into this table as below:

  INSERT INTO EMPLOYEE(fullname, occupation, gender, salary, country)  VALUES (‘John Doe’, ‘Writer’, ‘Male’, 62000, ‘USA’),  (‘Mary Greenspan’, ‘Freelancer’, ‘Female’, 55000, ‘India’),  (‘Grace Smith’, ‘Scientist’, ‘Male’, 85000, ‘USA’),  (‘Mike Johnson’, ‘Manager’, ‘Male’, 250000, ‘India’),  (‘Todd Astel’, ‘Business Analyst’, ‘Male’, 42000, ‘India’),  (‘Sara Jackson’, ‘Engineer’, ‘Female’, 65000, ‘UK’),  (‘Nancy Jackson’, ‘Writer’, ‘Female’, 55000, ‘UK’),  (‘Rose Dell’, ‘Engineer’, ‘Female’, 58000, ‘USA’),  (‘Elizabeth Smith’, ‘HR’, ‘Female’, 55000, ‘UK’),  (‘Peter Bush’, ‘Engineer’, ‘Male’, 42000, ‘USA’);  

Execute the SELECT statement to verify the table data:

In the above table, we can see that we have got employees from three different countries US, UK, and India. Now, based on these table data, we want to write a query to retrieve salaries grouped by country as below:

  SELECT country, SUM(salary) AS “Total Salary”  FROM EMPLOYEE  GROUP BY country;  

Executing the query will return the following output:

This query is good if we want to get the total salary based on country. But it’s not a better option when we want to get the grand totals of all salaries in the result also because it can only aggregate data at one level, which is subtotal, not grand totals. Hence, we will use the ROLLUP clause along with GROUP BY to examine both the subtotal and the grand totals for a category as it extends the capability of the GROUP BY clause.

The following query is used to retrieve subtotal and grand totals of salaries grouped by country:

  SELECT country, SUM(salary) AS “Total Salary”  FROM EMPLOYEE  GROUP BY ROLLUP (country);  

We will get the below output:

We can also get the same output using the below syntax:

  SELECT country, SUM(salary) AS “Total Salary”  FROM EMPLOYEE  GROUP BY country WITH ROLLUP;  

Here is the result:

In the output, we can see the total salary for each country as well as returns the grand totals of all salaries. The ROLLUP clause will add an extra row in the result set to display the grand totals. Here NULL represents the grand totals. We can also give it a meaningful name using the COALESCE function to replace the NULL value with any provided text.

The below statement will substitute “Grand Total” for the null value:

  SELECT COALESCE(country, ‘GRAND TOTAL’ ) AS country, SUM(salary) AS “Total Salary”  FROM EMPLOYEE  GROUP BY ROLLUP (country);  

Here is the result that replaced the NULL value with the given text:

ROLLUP with multiple columns

We already know that the ROLLUP clause performs aggregate operations on multiple levels in a hierarchy and prepare summary reports at each level within a single query.

Let’s understand this concept with an example. Suppose there are multiple columns in the GROUP BY clause. In this case, the ROLLUP clause assumes a hierarchy among the columns supplied in the GROUP BY clause. The query adds an extra super-aggregate summary row to the end of the result whenever a column value changes.

For example, we have specified the three columns in the GROUP BY clause as below:

The ROLLUP modifier assumes the hierarchy as below:

And generates the following grouping set:

The following query helps to understand it more clearly:

  SELECT gender, country, SUM(salary) AS “Total Salary”  FROM EMPLOYEE  GROUP BY gender, country  

Since we have not used the ROLLUP clause, a summary of the employee table based on multiple columns supplied in the GROUP BY clause would look like the below output. In this case, we will only receive summary values at the gender/country level of analysis.

Now we will analyze the output with the ROLLUP clause:

  SELECT gender, country, SUM(salary) AS “Total Salary”  FROM EMPLOYEE  GROUP BY gender, country WITH ROLLUP;   

Executing the query will display the following output where we can see the several extra rows:

This output generates data at three different levels of analysis, which are described below:

The first level generates an extra sub-aggregate summary row for each set of female employees and then displays the total salary for all employees. It will set the country column to NULL.
The second level generates an extra sub-aggregate summary row for each set of male employees and then displays the total salary. It will also set the country column to NULL.
Finally, the third level will generate an extra super-aggregate summary row that shows the grand totals for all columns. It will set the gender and country columns to NULL.

It ensures that we get a different answer if we modify the order of the columns defined in the GROUP BY clause. See the below query:

  SELECT gender, country, SUM(salary) AS “Total Salary”  FROM EMPLOYEE  GROUP BY country, gender WITH ROLLUP;  

Here is the result:

GROUPING Function with ROLLUP

The GROUPING() function determines whether NULL in the output is a regular grouped value, a super-aggregate value, or grand totals. It yields one when NULL occurs in a supper-aggregate row. Otherwise, it returns 0. The GROUPING() function can be used in the select list, HAVING clause, and ORDER BY clause.

See the below query:

  SELECT gender, country, SUM(salary) AS “Total Salary”,  GROUPING(gender),    GROUPING(country)  FROM EMPLOYEE  GROUP BY   ROLLUP (gender, country);  

Execute the query will return the below output:

In the output, we can see that the GROUPING(gender) returns one when NULL in the gender column occurs in a super-aggregate row. Otherwise, it will return zero.

Similarly, the GROUPING(country) returns one when NULL in the country column occurs in a super-aggregate row. Otherwise, it will return zero.

How is ROLLUP different from CUBE?

ROLLUP and CUBE in SQL Server are used for reporting purposes and display the subtotal and grand totals. We generally use these clauses with the GROUP BY clause. However, both are used for similar objectives, but they have some differences also.

ROLLUP returns an output that shows aggregates for a hierarchy of values in the columns you’ve chosen. On the other hand, CUBE creates a result set that includes aggregates for all possible combinations of values in the columns that have been selected.

Let us take an example to illustrate these differences. Suppose we have three columns c1, c2, and c3. Next, we will perform aggregation at a different level.

If we use these columns with the CUBE clause as CUBE(c1, c2, c3), we will get the eight possible grouping sets:

If we use these columns with the ROLLUP clause as ROLLUP(c1, c2, c3), we will get only four possible grouping sets:

Next TopicSQL Server CROSS JOIN

SQL Server ROLLUP

SQL Server ROLLUP

NOTE: ROLLUP modifier produces the summary output, including extra rows that represent super-aggregate summary operations. It is mainly used to provide support for OLAP (Online Analytical Processing) operations.

Syntax

SQL Server ROLLUP Example

ROLLUP with multiple columns

GROUPING Function with ROLLUP

How is ROLLUP different from CUBE?

SQL Server RENAME Database

How to Delete Column from Table in SQL

You may also like