WITH ROLLUP & GROUPING

WITH ROLLUP can be used after the GROUP BY clause in an aggregate query. It will take the GROUP BY
elements one by one from the right and remove them and run the query, with that group by clause (nulling out the unused GROUP by columns ), then append those results to the Result Set.

The GROUPING Function is also supported in the SELECT statement. It takes a column as a parameter. It
returns 0 if the column is in the GROUP BY, and 1 if it is not.

Example:

Returns the Number Of Orders by Customer, Employee, then the number of orders by Customer, and then at the bottom, just the total number of orders. The last two columns will display 0,0 for the first part, 0,1 for the customer subtotals and 1,1 for the last row. This clause is useful to add higher level subtotals to an aggregate query.

select customerid, employeeid, sum(1) as NoOfOrders,
grouping(customerid),
grouping(employeeid)
from orders
group by customerId,Employeeid
with rollup