Standard GROUP BY is simple. But what if your boss wants the Total Sales by Category, and also the Sub-total by Category/Year, and also the Grand Total, all in ONE result set? This is where GROUPING SETS come in.
SELECT Category, Year, SUM(Sales)
FROM Orders
GROUP BY ROLLUP(Category, Year)Remember: WHERE filters individual rows before they are grouped. HAVING filters the groups after the calculation is done. You cannot put SUM() in a WHERE clause!
Q: "What is the 'GROUPING()' function used for in a ROLLUP result?"
Architect Answer: "When you use ROLLUP, the 'Grand Total' row will have NULL in the Category column. But what if a real category is actually named 'NULL'? The `GROUPING()` function returns a '1' if the column is a generated summary row and '0' if it is actual data. This allows you to replace'NULL' with the word 'Grand Total' in your report safely."