In reporting and analytics, we often need to rotate data — turning rows into columns or columns into rows.SQL provides two powerful operators for this purpose: PIVOT and UNPIVOT. Let’s explore them step by step with examples from the HR schema. Example 1: Pivoting Employee Count by Job and Department This query counts employees by […]
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 […]