Syntax:
The ROLLUP is used to create a group of columns. So any aggregate function of MySQL and GROUP BY clause are used in the SELECT query where the ROLLUP is used. The syntax of the ROLLUP is shown below. The field names of the table that will be used after the SELECT clause will be used with the GROUP BY clause for grouping.
FROM TableName
GROUP BY field1, filed2 WITH ROLLUP;
Uses of ROLLUP statement in SELECT query:
Different uses of the ROLLUP statement with the GROUP BY clause in the SELECT query have been shown in this tutorial.
Pre-requisites:
You have to create a database table with data in a MySQL database to check the use of the ROLLUP statement with the GROUP BY clause in MySQL. Open the terminal and connect with the MySQL server by executing the following command.
Run the following command to create a database named test_db.
Run the following command to select the database.
Run the following query to create a table named sales_persons with four fields.
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL,
email VARCHAR(50),
contact_no VARCHAR(30));
Run the following query to insert three records into the sales_person table.
(NULL, 'Nila Hossain', '[email protected]', '01855342357'),
(NULL, 'Abir Hossain', '[email protected]', '01634235698');
Run the following query to create a table named sales with four fields that contains a foreign key that creates a one-to-many relationship with the sales_persons table to the sales table.
id INT NOT NULL PRIMARY KEY,
sales_date DATE NOT NULL, amount INT,
sp_id int,
CONSTRAINT fk_sp FOREIGN KEY (sp_id)
REFERENCES sales_persons(id)
ON DELETE CASCADE ON UPDATE CASCADE);
Run the following query to insert four records into the sales table.
(90, '2021-11-09', 800000, 1),
(34, '2020-12-15', 563400, 3),
(67, '2021-12-23', 900000, 1),
(56, '2020-12-31', 6700000, 1);
Example-1: Create summary report without using ROLLUP statement
The following SELECT query will calculate the summary report of the total sales amount of each salesperson and the overall sales amount of all sales by using UNION ALL with two SELECT queries. One SELECT query will retrieve the salesperson’s name and total sales of that salesperson. Another query will calculate the sales of all salespersons. Execute the following query from the mysql prompt.
FROM sales, sales_persons
WHERE sales.sp_id = sales_persons.id
GROUP BY sp_id
UNION ALL
SELECT NULL, SUM(sales.amount) AS Total
FROM sales;
Output:
The following output will appear after executing the above query. There is one record for the salesperson named ‘Abir Hossain’ with the id value 3 in the sales table, and the amount is 563400. There are three records for the salesperson named ‘Kamal Hasan’ with the id value, 1 in the sales table, and the total sales amount is 8400000. The sum of all sales amount is 563400+8400000 = 8963400.
Output:
The following output will appear after executing the above query. The name field will show the NULL value for the row that contains the total amount.
Example-2: Use of ROLLUP with a field and SUM() function
The output generated in the previous example can be generated easily by using the ROLLUP statement that has been shown in this example. The SELECT query that has been used to count the total sales amount of all salespersons in the previous example is not required in the following query because of using the ROLLUP statement. The name field of the sales table has been used with the aggregate function in the SELECT query. The GROUP BY clause contains the name field with the ROLLUP statement.
FROM sales,sales_persons
WHERE sales.sp_id = sales_persons.id
GROUP BY name WITH ROLLUP;
Output:
The following output will appear after executing the above query. Like the previous example, the name field will show the NULL value for the row that contains the total amount.
Example-3: Use of ROLLUP with YEAR() function alongside the SUM() function
The use of any MySQL built-in function with the aggregate function for using the ROLLUP statement has been shown in this example. The following SELECT query will show the summary report of the sales amount based on the sales year. The Year() function has been used in the query to find out the year value from the sales date. According to the inserted data of the sales table, there are two entries for 2020 and two entries for the year 2021.
FROM sales
GROUP BY Year(sales_date) WITH ROLLUP;
Output:
The following output will appear after executing the above query. The total sales amount for the year 2020 is 563400+6700000 or 7263400. The total sales amount for the year 2021 is 800000+900000 or 1700000. The total of 7263400+1700000 is 8963400.
Conclusion:
Many complex queries can be easily implemented by using the ROLLUP statement. The uses of this statement have been shown by using multiple tables in this tutorial for helping the new MySQL users.
