Learn SQL Database Programming

Learning how to use the ROLLUP modifier

The ROLLUP modifier allows you to get subtotal rows (also referred to as super-aggregate rows) and a grand total row. The ROLLUP modifier works by aggregating the grouping sets in a GROUP BY clause. A grouping set is the set of columns you group by. For example, with the following query, the grouping set is the sum of at-bats by playerid and teamid:

USE lahmansbaseballdb; SELECT playerid, teamid, sum(AB) AS sum_at_batsFROM batting GROUP BY playerid, teamid;

To get a subtotal by grouping set and grand total, you will add the ROLLUP modifier, like so:

USE lahmansbaseballdb; SELECT playerid, teamid, sum(AB) AS sum_at_batsFROM batting GROUP BY playerid, teamid WITH ROLLUP; 

If you scroll down to the bottom ...