Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
GROUP BY vs Rollup?
3.
Syntax of Rollup MySQL
4.
Example 1
4.1.
NULL Value in Rollup MySQL
5.
Example 2
6.
Limitations of ROLLUP
7.
Performance Considerations of ROLLUP
8.
Frequently Asked Questions
8.1.
How to differentiate between the actual NULL value and the NULL values generated by RollUp?
8.2.
Is there any substitute for generating reports in MySQL other than Rollup?
8.3.
Custom sorting of subtotals and grand totals is supported by Rollup or not?
9.
Conclusion
Last Updated: Mar 27, 2024
Medium

Rollup MySQL

Author Dhruv Rawat
0 upvote
Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

Introduction

MySQL is an open-source relational database management system. It offers a range of powerful features to manipulate and analyse data. One such feature is the "ROLLUP" operation.

rollup mysql


It helps users to understand their data comprehensively by generating summary reports. It also provides convenient and efficient ways to perform operations on aggregated data.

Rollup is used with GROUP BY clause to provide extra rows to the output that represents subtotals and grand totals. 

Inorder to understand the Rollup Clause we need to first understand what a GROUP BY clause is. 

So continue reading this article to understand Rollup MySQL completely with its syntax, use cases, limitations and performance considerations.  

GROUP BY vs Rollup?

Just Imagine you have a large box of candy and the box contains chocolates, lollipops and gum in it, and you want to know how much candy is in the box. You are a smart person and start grouping the candy by their type. You have one group for chocolate, lollipops and gums respectively. You can then count the number of candies in each group to figure out the type of candies each box contains.

The GROUP BY clause in MYSQL is like grouping the candies in the box. It helps to summarise the data by grouping rows together based on the values in one or more columns. 

The ROLLUP modifier is like adding an extra row to the output, which represents the total of all the groups. So it will show you the total number of candies in the box by creating an extra row.

Now let's see the difference in their outputs by applying each on a candy table created on MySQL.

CREATE TABLE candy (
  type VARCHAR(255),
  quantity INT
);


INSERT INTO candy (type, quantity) VALUES
  ('chocolate', 10),
  ('gums, 20),
  ('lollipops', 30);


SELECT type, SUM(quantity) AS total_quantity
FROM candy
GROUP BY type;


This query will return the following output:

output using group by only


The above output is clearly showing the total quantities of each type of candy.
 

Now let's add the Rollup modifier to see the change in the output.

SELECT type, SUM(quantity) AS total_quantity
FROM candy
GROUP BY type WITH ROLLUP;


This query will return the following output:

Output using rollup modifier


Clearly you can see the output now includes an additional row that shows the grand total of all the candies.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Syntax of Rollup MySQL

The basic syntax of ROLLUP MySQL is as follows:

SELECT column1, column2, ..., aggregate_function(column)
FROM table
GROUP BY column1, column2, ..., WITH ROLLUP(column)


In the above syntax, the aggregate_function can be any aggregate function like SUM, COUNT, AVG, etc. 

The ROLLUP(column) clause specifies the columns for generating the hierarchical summary.

Let us see some more examples of rollup in MySQL queries to perform calculations and generate results. 
 

The below table will be used to perform the calculations and generate summary reports.

table used for operation

Example 1

SELECT type, region, SUM(quantity) AS total_quantity
FROM candy
GROUP BY type, region WITH ROLLUP;


This query will return the following output:

 

output for example1


As you can see, the output shows the total quantity of each type of candy in each region and the grand total of all candies.


In addition, the output also shows subtotals for each region. For example, the subtotal for the "Delhi" region is 100, which is the total quantity of chocolate sold in Delhi. Similarly, gums in the "Gurgaon" region are 60.

Are you getting confused with the NULL values in the result table? Worry not; let's understand it.

NULL Value in Rollup MySQL

So when we use Rollup, MySQL adds extra rows representing the grand total and subtotal. These rows will contain NULL values for any columns that were not used to group the data.

For example 1, the NULL values in the rows indicate that there is no further grouping that can be done. 

For example,  the bottom row with NULL values in both type and region columns represents the total quantity of all candies, irrespective of type or region.

Example 2

SELECT type, year, region, SUM(quantity) AS total_quantity
FROM candy
GROUP BY type, year, region WITH ROLLUP;


This query will return the following output:

output for example 2


As you can see, the output shows the total quantity of each type of candy in each year and region, as well as the grand total of all candies.

In addition, the output also shows subtotals for each year and region. 

For example, the subtotal for the year 2023 in the "Delhi" region is 100, which is the sum of the total quantity of chocolate sold in the "Delhi" region in the year 2022. I hope this example helps you to understand how 

These examples provide on how to use ROLLUP to summarise data in MySQL.

Limitations of ROLLUP

ROLLUP has a few limitations:

  • MySQL ROLLUP cannot specify multiple grouping sets within a single query.
     
  • Using rollup with large data sets or complex queries can slow down its execution speed.
     
  • Rollup supports only standard functions like SUM, COUNT, AVG etc. but not BIT_OR types functions. So limited aggregate functions are supported by it.
     
  • Rollup generates null values for a subtotal and grand total, which makes it difficult to differentiate from the actual NULL value present in the table.

Performance Considerations of ROLLUP

Let's look at some important factors that could impact performance.

  • Rollup could slow down the execution process when applied to large data sets due to its increased computational requirements
     
  • Using necessary filters, joins, and appropriate indexing strategies could improve the performance of the rollup query.
     
  • Uneven data distribution or high cardinality values in groups by columns could affect the speed of query execution.
     
  • Properly aligning the group by and rollup column with index order can help in enhancing the execution of a query.

Frequently Asked Questions

How to differentiate between the actual NULL value and the NULL values generated by RollUp?

As Rollup generates null values for determining the grand total and subtotals, it is challenging to differentiate, but with the use of functions like IFNULL or COALESCE, we can easily differentiate between the NULL values.

Is there any substitute for generating reports in MySQL other than Rollup?

Yes, there are many other ways to achieve similar summary reports in MySQL. By using Techniques like CUBE and GROUPING SETS, or we can perform multiple queries with UNION ALL.

Custom sorting of subtotals and grand totals is supported by Rollup or not?

No, rollup does not support custom sorting; it automatically generates subtotals and grand totals based on the order of columns in the GROUP BY clause. Custom sorting can be done through additional calculations outside of RollUp.

Conclusion

Congratulations, you did a fantastic job!!. This article has gone through a comprehensive guide to MySQL Rollup and explained its syntax, usage scenarios, performance considerations and limitations At last, some frequently asked questions have been discussed.

Here are some more related articles:

Check out The Interview Guide for Product Based Companies and some famous Interview Problems from Top Companies, like AmazonAdobeGoogle, etc., on CodeStudio.

Also, check out some of the Guided Paths on topics such as Data Structure and AlgorithmsCompetitive ProgrammingOperating SystemsComputer Networks, DBMSSystem Design, etc., as well as some Contests, Test SeriesInterview Bundles, and some Interview Experiences curated by top Industry Experts only on CodeStudio.

We hope you liked this article.

"Have fun coding!”

Previous article
MySQL Variables
Next article
MySQL ORDER BY Keyword
Live masterclass