MySQL ROLLUP

GROUP BY Clause of the SELECT statement can be used with modifiers to enhance summarization of data.  MySQL ROLLUP is one such modifier to be used after GROUP BY to group data and add some common summarizations.

MySQL ROLLUP is used to add additional layers of summarized data that correspond to the number of groups possible with columns in GROUP BY clause. It helps in creating reports for data analysis. Different dimensions of data can be analyzed with MySQL ROLLUP

MySQL ROLLUP can be useful in situations like-

  • Display year wise total and overall total salaries in an organization.
  • Display region-wise, year-wise and total sales.

MySQL ROLLUP Syntax

SELECT [All| DISTINCT ] Column Name1 AS alias,Column Name2,…, [SUM|AVG|MIN|MAX|COUNT(Column Name11,)…]FROM table name [WHERE condition expression] [GROUP BY Column Name1, Column Name2,…[WITH ROLLUP] ]
  • The Syntax of the SELECT with GROUP BY clause is explained in MySQL GROUP BY topic.
  • WITH ROLLUP is added after the columns names given for grouping in GROUP BY clause. ROLLUP is done from left to right for columns in the GROUP BY clause.

Examples of MySQL ROLLUP

Find Job ID wise and overall average salaries

The following query displays average salaries for all the job ids with rollup and average salary for all Job ids

SELECT job_id , AVG(salary) FROM employees GROUP BY job_id WITH ROLLUP;
MySQL Rollup Example

Find Job ID wise and overall total salaries with WHERE

This query displays job_id wise total salaries in the company.

SELECT job_id, SUM(salary) FROM employees WHERE salary>6000 GROUP BY job_id WITH ROLLUP;
with WHERE

Find Job ID wise and overall total salaries with HAVING

This query finds job wise total salaries having total salaries between 20000 and 30000.

SELECT job_id, SUM(salary) FROM employees GROUP BY job_id WITH ROLLUP HAVING SUM(salary) BETWEEN 20000 AND 30000;
with HAVING

Find Department, Job ID wise and overall count of employees

This query finds Department, Job ID wise and overall count of employees. The ROLLUP summarization columns have NULL for summarized data for groups. 

SELECT department_id, job_id, count(salary) FROM employees GROUP BY department_id, job_id WITH ROLLUP;
multiple column rollup

Check Job_id column in 3rd, 6th, 8th, 11th , 14th and 15th rows in above output . These are summarized count of employees for departments with different job_ids. So, the corresponding value for job_id is NULL.

Points to remember

  • The non-aggregate column names in the SELECT part of the query must be included in the GROUP BY clause before WITH ROLLUP clause.
  • WHERE clause and HAVING clauses can be used to filter and include specific rows for summarizing the table data before and after summarization.
  • The higher level summarization column value is displayed as NULL.