Advanced Aggregations

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.

1. ROLLUP and CUBE

• ROLLUP: Generates hierarchical summaries (Category -> Grand Total).
• CUBE: Generates every possible cross-calculation of your columns. (Category, Year, and Category+Year).

SELECT Category, Year, SUM(Sales)
FROM Orders
GROUP BY ROLLUP(Category, Year)

2. Filtering Aggregates: HAVING vs WHERE

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!

4. Interview Mastery

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."