How to Add Total Row in MySQL

Last updated on August 19th, 2020 at 04:43 am

Many times, you need to report data in a MySQL table, along with totals. There are 2 ways to add total row in MySQL, using UNION ALL and ROLLUP. Here’s how to add total row in MySQL.

Table of Contents

How to Add Total Row in MySQL

Here are the steps to add total row in MySQL. 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 |
+------------+------+

We will look at 2 ways to add total row in MySQL – using UNION ALL and ROLLUP. ROLLUP function was added to MySQL from probably version 5.0. So those working with older versions will need to use UNION ALL.

Using UNION ALL

In this approach, we SUM the data and simply append it to our original table using UNION ALL clause, as shown below

mysql> select * from sales
       UNION ALL
       select 'Total' order_date,sum(sale) 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 |
| Total      |  220 |
+------------+------+

In the above query, the total row displayed as last row is appended to your original table using UNION ALL. This doesn’t change the original table, but only the displayed result. While using UNION ALL, it is necessary to maintain the same columns in all select queries used in it. That is why, we have assigned a text ‘Total’ as order_date column. UNION ALL is an old-fashioned way to add total in MySQL.

Bonus Read : How to Calculate Running Total in MySQL

Using ROLLUP

You can also use GROUP BY with ROLLUP clause to directly total values and automatically add total row in MySQL, as shown below

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 |
+------------+------+

Using ROLLUP is shorter and also helps you add summary row with totals, but it doesn’t allow you to add custom text like ‘Total’ in your last row. Another problem is that it will automatically rollup data for multiple levels, add sub total rows as well as grand total row..

Here’s an example to add total row in MySQL, using 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 Concatenate Multiple Rows into One Field in MySQL

No matter how you specify your GROUP BY clause, you will invariably end up with NULLs and subtotals, when you add total row in MySQL, when you use ROLLUP.

GROUP BY product and order_date
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 |
+---------+------------+-----------+

GROUP BY only product
mysql> select product,order_date,sum(sale) from sales group by product with rollup;
+---------+------------+-----------+
| product | order_date | sum(sale) |
+---------+------------+-----------+
| A       | 2020-01-01 |        70 |
| B       | 2020-01-02 |        40 |
| NULL    | 2020-01-02 |       110 |
+---------+------------+-----------+

GROUP BY order_date
mysql> select product,order_date,sum(sale) from sales group by order_date with rollup;
+---------+------------+-----------+
| product | order_date | sum(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 |
| A       | NULL       |       110 |
+---------+------------+-----------+

However, as mentioned before, it is a very convenient way of quickly calculating subtotals, and add total row in MySQL.

So if you want to customize your total row, go with UNION ALL as it gives you more control. On the other hand, if you want to calculate sub totals too, then go with ROLLUP.

That’s it! Now you know how to add total row in MySQL.

You can use a reporting tool to plot this data in a table and share it with your team. Here’s an example of a table created using Ubiq.