EDUCBA

Home Data Science Data Science Tutorials MySQL Tutorial ROLLUP in MySQL

ROLLUP in MySQL

By Aanchal SharmaAanchal Sharma

ROLLUP in MySQL

Introduction to ROLLUP in MySQL

MySQL ROLLUP is a type of SQL Keyword used in the statement with GROUP BY Clause that helps to create subtotals as well as grand totals for the result set of columns as a summary row. The ROLLUP operator is used in MySQL with GROUP BY Statement as an extension or an advance feature to filter the sum total for a column or a group of columns by adding additional rows.

As we know, the GROUP BY query is applied with aggregate functions like COUNT, MAX, MIN, SUM, AVG which groups the result rows by single or more columns. The ROLLUP SQL operator is an option to use GROUP BY Clause to allow you to include extra fields representing the subtotals. These subtotal rows are referred to as super-aggregate rows in combination with the grand total row. So, we can create multiple grouping of set rows by using a single query containing both the GROUP BY Clause and ROLLUP extension in MySQL.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax:

Supported by SQL Server | Oracle | MS Access

SELECT r1, r2 AggregateFunction(r3) FROM TableName GROUP BY ROLLUP (r1,r2);

For MySQL, we use this syntax which is slightly different from above:

SELECT r1, r2 AggregateFunction(r3) FROM TableName GROUP BY r1, r2 WITH ROLLUP;

Since ROLLUP follows a hierarchy of input columns, so from the above syntax when we provide (r1, r2) then, it assumes the hierarchy r1 > r2. ROLLUP creates a group of columns that is sensible using this hierarchy. Therefore, in MySQL for generating reports we consider the ROLLUP clause to produce subtotals and grand totals.

How ROLLUP works in MySQL?

  • The ROLLUP operator can be said as an option to retrieve the result set with subtotals calculated on the basis of the grouped columns using GROUP BY. This produces the summarized amounts which is based on the table columns provided to the ROLLUP keyword in MYSQL.
  • Hence, the result table formed by using the ROLLUP operator collects the data from the grouping of sets of columns at a level of specifications and rolling them up to the main table.
  • Logically, we can justify that a ROLLUP operator is a SQL modifier that when used with GROUP BY Clause in the statement. Thus it affects the summary output by including extra rows that determine high level of instant operations in the table.
  • You can also add the LIMIT Clause at the same time to limit the rows in the result set with the ROLLUP clause.
  • The ROLLUP is not used with ORDER BY We can use ASC or DESC type of sorting in the GROUP BY clause using ROLLUP operator in SQL statement.

Examples of ROLLUP in MySQL

Given below are the examples of ROLLUP in MySQL:

For example, we have used here Customer_Data table with columns (ID, Name, Age, Address, Salary) in the Database PersonDb.

Customer_Data table

ROLLUP in MySQL 1

Example #1

MySQL ROLLUP with one column.

We are executing the below SQL Query to fetch the total salary by Customer name using GROUP BY Clause and the SQL SUM () aggregate function in MySQL.

SELECT Name, SUM(Salary) FROM `customer_data` GROUP BY Name;

Output:

with one column

Now, let us query the following statement to retrieve the total amount in all salary column using ROLLUP operator to the above SQL syntax code:

SELECT Name, SUM(Salary) FROM `customer_data` GROUP BY Name WITH ROLLUP;

Output:

ROLLUP in MySQL 3

In the above result set, you can view that the grand total line is specified by the NULL value column in the table. Here, the ROLLUP adds an extra row to display the grand total super aggregate row of all salary amounts form the table data.

Hence, we can make the result ROLLUP row more informative to users by using SQL COALESCE() function which is responsible to substitute the column value NULL with any other text word we want, suppose for here we have used “All Customers”.

SELECT COALESCE(Name, 'All Customers ') AS Name, SUM(Salary) FROM `customer_data` GROUP BY Name WITH ROLLUP;

Output:

ROLLUP in MySQL 4

Example #2

MySQL ROLLUP with Multiple columns.

The following code calculates the Customer_Data by two columns Name and Address:

SELECT Name, Address, SUM(Salary) FROM `customer_data` GROUP BY Name, Address;

Output:

with Multiple columns

Now, adding ROLLUP to the above SQL GROUP BY Statement:

SELECT Name, Address, SUM(Salary) FROM `customer_data` GROUP BY Name, Address WITH ROLLUP;

Output:

ROLLUP in MySQL 6

If you see the result table, it includes the output summary of rows not only at a single level but at two levels in the analysis process with ROLLUP.

  • For every set of Address rows of a particular Name, there is an extra row summarized that shows the total Customer_Data salary. Thus, this Address value in the columns is defined as NULL.
  • At the end of the result table, an additional row is present with the grand total amount of salary of all Name and Address columns whose values are set to NULL.

Example #3

MySQL ROLLUP with Partial rollup.

We can also perform operations in MySQL using ROLLUP and GROUP BY clauses in a partial way rollup. This helps to lessen the number of subtotals that are calculated.

SELECT DISTINCT Name, Address, SUM(Salary) FROM `customer_data` GROUP BY Address WITH ROLLUP;

Here, from the result set, we can clarify that the super aggregate grand total is calculated for the Address column only using ROLLUP clause and not for the name columns.

Therefore, we can make an analysis of multiple levels of operations using only a single SQL query in MySQL.

Conclusion

The ROLLUP operator in MySQL is used in a table to generate more than one grouping set of columns that you want to group with. From this, the SQL ROLLUP can also form grand total of the columns and not only just subtotals. Assume, when we are using multiple columns to write the query statement for a table using the ROLLUP and GROUP BY clause, then the SQL ROLLUP clause requires a hierarchy among these columns. Thus, the ROLLUP clause with the GROUP BY clause in MySQL helps to produce subtotal of the row each time when a new column changes and finally calculates grand total at the end.

Recommended Articles

This is a guide to ROLLUP in MySQL. Here we discuss the introduction, how ROLLUP works in MySQL? and examples. You may also have a look at the following articles to learn more –

  1. Oracle Clauses
  2. MySQL MAX() Function
  3. MIN() in MySQL
  4. Left Outer Join in MySQL
  5. PostgreSQL ROLLUP | Examples
  6. Examples of MySQL IN Operator