GROUPING SETS make it possible to apply many GROUP BY clauses in one single query. Moreover, this new feature introduces the notion of a ROLLUP, which is an extra row added to the result set that gives a summary of the results as a super-aggregate of previously returned values. Let's give a very simple example using the employees table in the test database. Let's execute the following query in the phpMyAdmin web interface:
SELECT superior AS manager_id, SUM(salary)
FROM employees
WHERE superior IS NOT NULL
GROUP BY manager_id, salary;
Once executed, you should see this result:
The multiple GROUP BY clauses have allowed us to quickly see each individual salary of each employee under the supervision of each manager. If we now add the ROLLUP operator to the GROUP BY clauses, we will obtain this result:
The ROLLUP operator adds extra rows that contain a super-aggregate result for each subset and for the entire result set. The execution plan reveals that the underlying select_type is once more SIMPLE instead of having multiple queries united with a UNION operator as we would have done before this feature existed. Once more, Modern SQL offers us a highly optimized query which will remain highly maintainable for many years to come.