MySQL: WITH ROLLUP

MySQL's WITH ROLLUP modifier when used in conjunction with GROUP BY allows users to produce multiple levels of summary values. For instance, suppose you need to generate a listing of the average and total salary by department for a company but also the average and total for the entire company. You could accomplish this using 2 separate queries as follows:

->SELECT department, avg(salary) AS salary_average, sum(salary) AS salary_total
->FROM employees
->GROUP BY department
+------------+----------------+--------------+
| department | salary_average | salary_total |
+------------+----------------+--------------+
| Accounting |         234567 |      2000001 |
| Creative   |          12000 |       100000 |
| Tech       |           2000 |        50002 |
+------------+----------------+--------------+
->SELECT avg(salary) AS salary_average, sum(salary) AS salary_total
->FROM employees
+----------------+--------------+
| salary_average | salary_total |
+----------------+--------------+
|         123456 |      2150003 |
+----------------+--------------+

Using WITH ROLLUP you could perform the preceding operation with just one query:

->SELECT department, avg(salary) AS salary_average, sum(salary) AS salary_total
->FROM employees
->GROUP BY department WITH ROLLUP
+------------+----------------+--------------+
| department | salary_average | salary_total |
+------------+----------------+--------------+
| Accounting |         234567 |      2000001 |
| Creative   |          12000 |       100000 |
| Tech       |           2000 |        50002 |
| NULL       |         123456 |      2150003 |
+------------+----------------+--------------+

The difference is in the last line where for department a NULL value is given along with a super-aggregate value for both the average and sum. The WITH ROLLUP modifier basically adds another row to the query results with the values obtained from the original query but without the GROUP BY clause. This method is more efficient than using 2 queries since the data need only be scanned once.