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;
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;
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;
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;
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.