Sign in to track progress and bookmarks.
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."
Quizzes linked to this course—pass to earn certificates.
On this page
1. ROLLUP and CUBE 2. Filtering Aggregates: HAVING vs WHERE 4. Interview Mastery