How to Use MySQL Rollup

Sometimes you may need to calculate totals and subtotals in MySQL, or add total rows to your data. It can be tedious to aggregate data using UNION statement especially to calculate subtotals. MySQL Rollup is suitable for such use cases. Here’s how to use MySQL Rollup to calculate total and subtotal  in MySQL.

 

How to Use MySQL Rollup

Here are the steps to calculate total and subtotal using MySQL Rollup.

MySQL Rollup allows you to easily roll up data and calculate subtotals using a single statement. You can also use it to add total rows and subtotal rows to your data, instead of calculating them separately and combining the result using UNION statement.

Here’s the syntax of MySQL ROLLUP.

SELECT 
    column1, column2, column3, ...
FROM 
    table_name
GROUP BY
    column1, column2,... WITH ROLLUP;

 

The above query is very similar to aggregating data using GROUP BY statement, except that you add WITH ROLLUP keyword after GROUP BY clause.

In the above query, we specify the columns we need to display in our query result in SELECT clause. We also need to mention the table name. In GROUP BY clause, we specify the columns based on which we want to aggregate data.

Bonus Read : How to Check MySQL Version

 

Let’s say you have the following table sales(order_date, sale).

mysql> create table sales(order_date date,sale int);
 
mysql> insert into sales values('2020-01-01',20),
('2020-01-02',25),('2020-01-03',15),('2020-01-04',30),
('2020-02-05',20),('2020-02-10',20),('2020-02-06',25),
('2020-03-07',15),('2020-03-08',30),('2020-03-09',20);
 
mysql> select * from sales;
+------------+------+
| order_date | sale |
+------------+------+
| 2020-01-01 |   20 |
| 2020-01-02 |   25 |
| 2020-01-03 |   15 |
| 2020-01-04 |   30 |
| 2020-02-05 |   20 |
| 2020-02-10 |   20 |
| 2020-02-06 |   25 |
| 2020-03-07 |   15 |
| 2020-03-08 |   30 |
| 2020-03-09 |   20 |
+------------+------+

Bonus Read : How to Disable Foreign Key Check in MySQL

 

Here’s the SQL query for MySQL Rollup to calculate total values and add a total row to the table. The grand total is always added as the last row, with the GROUP BY column being assigned NULL value.

mysql> select * from sales 
       group by order_date with rollup;
+------------+------+
| order_date | sale |
+------------+------+
| 2020-01-01 |   20 |
| 2020-01-02 |   25 |
| 2020-01-03 |   15 |
| 2020-01-04 |   30 |
| 2020-02-05 |   20 |
| 2020-02-06 |   25 |
| 2020-02-10 |   20 |
| 2020-03-07 |   15 |
| 2020-03-08 |   30 |
| 2020-03-09 |   20 |
| NULL       |   20 |
+------------+------+

In the above query we ROLLUP by order_date column.

Bonus Read : How to Create Sequence in MySQL

 

If you specify more than one column in GROUP BY clause, then MySQL will assume a hierarchy between them and rollup data accordingly.

For example, if you mention

GROUP BY c1, c2, c3 WITH ROLLUP

then MySQL will assume

c1 > c2 > c3

So it is important to be careful with the order of columns in GROUP BY clause, while using MySQL Rollup. Let’s say you have the following table.

mysql> create table sales(product varchar(255),order_date date,sale int);

mysql> insert into sales values('A','2020-01-01',20),('B','2020-01-02',25),
('B','2020-01-03',15),('A','2020-01-04',30),('A','2020-01-05',20);

mysql> select * from sales;
+---------+------------+------+
| product | order_date | sale |
+---------+------------+------+
| A       | 2020-01-01 |   20 |
| B       | 2020-01-02 |   25 |
| B       | 2020-01-03 |   15 |
| A       | 2020-01-04 |   30 |
| A       | 2020-01-05 |   20 |
+---------+------------+------+

Bonus Read : How to Compare Two Tables in MySQL

 

Here’s the SQL query to rollup data by product and order_date columns. In this case, MySQL will not only calculate grand total and display at the end but also calculate column-wise subtotals for each column mentioned in GROUP BY clause except the last one, that is, only product column.

mysql> select product,order_date,sum(sale) 
       from sales group by product,order_date with rollup;
+---------+------------+-----------+
| product | order_date | sum(sale) |
+---------+------------+-----------+
| A       | 2020-01-01 |        20 |
| A       | 2020-01-04 |        30 |
| A       | 2020-01-05 |        20 |
| A       | NULL       |        70 |
| B       | 2020-01-02 |        25 |
| B       | 2020-01-03 |        15 |
| B       | NULL       |        40 |
| NULL    | NULL       |       110 |
+---------+------------+-----------+

In the above result, you will see product-wise totals and grand total highlighted as bold

Here’s the result if you mention order_date and then product column in GROUP BY clause. In this case, MySQL will calculate order_date-wise total and grand total.

mysql> select product,order_date,sum(sale) 
       from sales group by order_date,product with rollup;	   

+---------+------------+-----------+
| product | order_date | sum(sale) |
+---------+------------+-----------+
| A       | 2020-01-01 |        20 |
| NULL    | 2020-01-01 |        20 |
| B       | 2020-01-02 |        25 |
| NULL    | 2020-01-02 |        25 |
| B       | 2020-01-03 |        15 |
| NULL    | 2020-01-03 |        15 |
| A       | 2020-01-04 |        30 |
| NULL    | 2020-01-04 |        30 |
| A       | 2020-01-05 |        20 |
| NULL    | 2020-01-05 |        20 |
| NULL    | NULL       |       110 |
+---------+------------+-----------+

 

As you can see the results are different if you change the order of columns mentioned in GROUP BY clause. Nevertheless, MySQL ROLLUP is a very convenient way of quickly calculating subtotals, and add total row in MySQL.

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!