Ranking functions are part of SQL analytic functions and are widely used in reporting, analytics, and business intelligence. The most common ones are RANK, DENSE_RANK, and ROW_NUMBER. While they seem similar, their behavior differs when handling duplicate values. Let’s explore them through practical examples. Example 1: Ranking Employees by Salary within Each Department Here we […]
When working with ROLLUP or CUBE, you’ll notice NULL values appear in subtotals and grand totals.But how do you tell the difference between a real NULL value in the data and a NULL that just means “subtotal here”? That’s where GROUPING_ID comes in. It returns a numeric code indicating which columns are aggregated (rolled up). […]
SQL provides powerful extensions to GROUP BY: ROLLUP and CUBE. These are extremely useful for building reports with subtotals and cross-tab summaries. Let’s see some practical examples. Example 1: Total Salaries with ROLLUP by Department and Job This query calculates total salaries: Output: The hierarchy is clear: job totals roll up into department totals, which […]
When you prepare reports, you often need numbers at different levels: detailed rows, subtotals, and a grand total. Without GROUPING SETS, you would write multiple queries and stitch them together with UNION ALL. With GROUPING SETS, one query does it all — more readable, more efficient. Let’s explore this using the HR schema tables (employees, […]