SQL Reporting - Aggregations, Pivots, and Analytics
Advanced Aggregations
GROUP BY with ROLLUP
ROLLUP creates subtotals and grand totals.
-- Sales by category and product with subtotals
SELECT
c.CategoryName,
p.ProductName,
SUM(oi.Quantity * oi.UnitPrice) AS TotalSales
FROM Orders o
INNER JOIN OrderItems oi ON o.OrderID = oi.OrderID
INNER JOIN Products p ON oi.ProductID = p.ProductID
INNER JOIN Categories c ON p.CategoryID = c.CategoryID
GROUP BY ROLLUP(c.CategoryName, p.ProductName)
ORDER BY c.CategoryName, p.ProductName;
/*
Result:
CategoryName ProductName TotalSales
-----------------------------------------
Electronics Laptop 5000.00
Electronics Mouse 150.00
Electronics NULL 5150.00 <- Subtotal
Books Fiction 800.00
Books Non-Fiction 600.00
Books NULL 1400.00 <- Subtotal
NULL NULL 6550.00 <- Grand Total
*/
GROUP BY with CUBE
CUBE creates all possible subtotal combinations.
-- Sales by year, quarter, and category with all combinations
SELECT
YEAR(o.OrderDate) AS Year,
DATEPART(QUARTER, o.OrderDate) AS Quarter,
c.CategoryName,
SUM(o.TotalAmount) AS TotalSales
FROM Orders o
INNER JOIN OrderItems oi ON o.OrderID = oi.OrderID
INNER JOIN Products p ON oi.ProductID = p.ProductID
INNER JOIN Categories c ON p.CategoryID = c.CategoryID
GROUP BY CUBE(
YEAR(o.OrderDate),
DATEPART(QUARTER, o.OrderDate),
c.CategoryName
);
/*
Creates subtotals for:
- Each year
- Each quarter
- Each category
- Each year + quarter
- Each year + category
- Each quarter + category
- Grand total
*/
GROUPING SETS
Specify exact grouping combinations you want.
-- Sales by category only AND by product only (two separate groups)
SELECT
c.CategoryName,
p.ProductName,
SUM(oi.Quantity * oi.UnitPrice) AS TotalSales
FROM Orders o
INNER JOIN OrderItems oi ON o.OrderID = oi.OrderID
INNER JOIN Products p ON oi.ProductID = p.ProductID
INNER JOIN Categories c ON p.CategoryID = c.CategoryID
GROUP BY GROUPING SETS (
(c.CategoryName), -- Totals by category
(p.ProductName), -- Totals by product
() -- Grand total
);
GROUPING() Function
Distinguish between NULL values and subtotal rows.
SELECT
c.CategoryName,
p.ProductName,
SUM(oi.Quantity) AS TotalQuantity,
GROUPING(c.CategoryName) AS IsCategorySubtotal,
GROUPING(p.ProductName) AS IsProductSubtotal,
CASE
WHEN GROUPING(c.CategoryName) = 1
AND GROUPING(p.ProductName) = 1 THEN 'Grand Total'
WHEN GROUPING(p.ProductName) = 1 THEN 'Category Subtotal'
ELSE 'Detail'
END AS RowType
FROM Orders o
INNER JOIN OrderItems oi ON o.OrderID = oi.OrderID
INNER JOIN Products p ON oi.ProductID = p.ProductID
INNER JOIN Categories c ON p.CategoryID = c.CategoryID
GROUP BY ROLLUP(c.CategoryName, p.ProductName);
PIVOT - Rows to Columns
Transform row data into columnar format.
Basic PIVOT
-- Sales by category (rows) and quarter (columns)
SELECT *
FROM (
SELECT
c.CategoryName,
DATEPART(QUARTER, o.OrderDate) AS Quarter,
o.TotalAmount
FROM Orders o
INNER JOIN OrderItems oi ON o.OrderID = oi.OrderID
INNER JOIN Products p ON oi.ProductID = p.ProductID
INNER JOIN Categories c ON p.CategoryID = c.CategoryID
) AS SourceData
PIVOT (
SUM(TotalAmount)
FOR Quarter IN ([1], [2], [3], [4])
) AS PivotTable;
/*
Result:
CategoryName 1 2 3 4
----------------------------------------
Electronics 1200 1500 1800 2000
Books 800 900 750 950
Toys 400 500 600 550
*/
PIVOT with Dynamic Columns
-- Get all years dynamically
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @columns = STRING_AGG(QUOTENAME(Year), ',')
FROM (SELECT DISTINCT YEAR(OrderDate) AS Year FROM Orders) AS Years;
SET @sql = N'
SELECT *
FROM (
SELECT
c.CategoryName,
YEAR(o.OrderDate) AS Year,
o.TotalAmount
FROM Orders o
INNER JOIN OrderItems oi ON o.OrderID = oi.OrderID
INNER JOIN Products p ON oi.ProductID = p.ProductID
INNER JOIN Categories c ON p.CategoryID = c.CategoryID
) AS SourceData
PIVOT (
SUM(TotalAmount)
FOR Year IN (' + @columns + ')
) AS PivotTable;
';
EXEC sp_executesql @sql;
UNPIVOT - Columns to Rows
Transform column data into row format (opposite of PIVOT).
-- Source table
CREATE TABLE QuarterlySales (
ProductName NVARCHAR(50),
Q1 DECIMAL(10,2),
Q2 DECIMAL(10,2),
Q3 DECIMAL(10,2),
Q4 DECIMAL(10,2)
);
-- UNPIVOT to normalize
SELECT
ProductName,
Quarter,
Sales
FROM QuarterlySales
UNPIVOT (
Sales FOR Quarter IN (Q1, Q2, Q3, Q4)
) AS UnpivotTable;
/*
Result:
ProductName Quarter Sales
----------------------------------
Laptop Q1 5000.00
Laptop Q2 6000.00
Laptop Q3 7000.00
Laptop Q4 8000.00
*/
Window Functions
Perform calculations across sets of rows without collapsing them.
ROW_NUMBER()
-- Rank products by price within each category
SELECT
CategoryID,
ProductName,
Price,
ROW_NUMBER() OVER (
PARTITION BY CategoryID
ORDER BY Price DESC
) AS PriceRank
FROM Products;
/*
CategoryID ProductName Price PriceRank
---------------------------------------------
1 Laptop 1200 1
1 Mouse 25 2
1 Keyboard 75 3
2 Encyclopedia 200 1
2 Novel 15 2
*/
RANK() and DENSE_RANK()
-- RANK() - leaves gaps after ties
-- DENSE_RANK() - no gaps after ties
SELECT
ProductName,
Price,
RANK() OVER (ORDER BY Price DESC) AS Rank,
DENSE_RANK() OVER (ORDER BY Price DESC) AS DenseRank
FROM Products;
/*
ProductName Price Rank DenseRank
-----------------------------------------
Laptop 1200 1 1
Tablet 1200 1 1 <- Tie
Monitor 800 3 2 <- RANK skips 2
Mouse 50 4 3
*/
NTILE()
Distribute rows into specified number of buckets.
-- Divide products into 4 price quartiles
SELECT
ProductName,
Price,
NTILE(4) OVER (ORDER BY Price) AS PriceQuartile
FROM Products;
/*
ProductName Price PriceQuartile
-------------------------------------
Mouse 25 1 <- Bottom 25%
Keyboard 75 1
Monitor 800 2
Tablet 1200 3
Laptop 1500 4 <- Top 25%
*/
LAG() and LEAD()
Access previous/next row values.
-- Compare each month's sales to previous month
SELECT
YEAR(OrderDate) AS Year,
MONTH(OrderDate) AS Month,
SUM(TotalAmount) AS MonthlySales,
LAG(SUM(TotalAmount)) OVER (ORDER BY YEAR(OrderDate), MONTH(OrderDate)) AS PrevMonthSales,
SUM(TotalAmount) - LAG(SUM(TotalAmount)) OVER (ORDER BY YEAR(OrderDate), MONTH(OrderDate)) AS MonthOverMonthChange
FROM Orders
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY Year, Month;
/*
Year Month MonthlySales PrevMonthSales Change
------------------------------------------------------
2025 1 5000 NULL NULL
2025 2 6000 5000 1000
2025 3 5500 6000 -500
*/
FIRST_VALUE() and LAST_VALUE()
-- Compare each product price to cheapest in category
SELECT
CategoryID,
ProductName,
Price,
FIRST_VALUE(Price) OVER (
PARTITION BY CategoryID
ORDER BY Price
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS CheapestInCategory,
Price - FIRST_VALUE(Price) OVER (
PARTITION BY CategoryID
ORDER BY Price
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS PriceDiffFromCheapest
FROM Products;
Running Totals
-- Running total of sales by date
SELECT
OrderDate,
TotalAmount,
SUM(TotalAmount) OVER (
ORDER BY OrderDate
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS RunningTotal
FROM Orders
ORDER BY OrderDate;
/*
OrderDate TotalAmount RunningTotal
--------------------------------------------
2025-01-01 100.00 100.00
2025-01-02 150.00 250.00
2025-01-03 200.00 450.00
*/
Moving Averages
-- 7-day moving average of sales
SELECT
OrderDate,
SUM(TotalAmount) AS DailySales,
AVG(SUM(TotalAmount)) OVER (
ORDER BY OrderDate
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS MovingAvg7Day
FROM Orders
GROUP BY OrderDate
ORDER BY OrderDate;
Common Report Patterns
Year-Over-Year Comparison
WITH YearlySales AS (
SELECT
YEAR(OrderDate) AS Year,
MONTH(OrderDate) AS Month,
SUM(TotalAmount) AS MonthlySales
FROM Orders
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
)
SELECT
curr.Month,
curr.Year AS CurrentYear,
curr.MonthlySales AS CurrentYearSales,
prev.Year AS PreviousYear,
prev.MonthlySales AS PreviousYearSales,
curr.MonthlySales - prev.MonthlySales AS YoYChange,
CASE
WHEN prev.MonthlySales = 0 THEN NULL
ELSE ((curr.MonthlySales - prev.MonthlySales) / prev.MonthlySales) * 100
END AS YoYChangePercent
FROM YearlySales curr
LEFT JOIN YearlySales prev
ON curr.Month = prev.Month
AND curr.Year = prev.Year + 1
ORDER BY curr.Year, curr.Month;
Top N Per Group
-- Top 3 products by sales in each category
WITH RankedProducts AS (
SELECT
c.CategoryName,
p.ProductName,
SUM(oi.Quantity * oi.UnitPrice) AS TotalSales,
ROW_NUMBER() OVER (
PARTITION BY c.CategoryName
ORDER BY SUM(oi.Quantity * oi.UnitPrice) DESC
) AS SalesRank
FROM Orders o
INNER JOIN OrderItems oi ON o.OrderID = oi.OrderID
INNER JOIN Products p ON oi.ProductID = p.ProductID
INNER JOIN Categories c ON p.CategoryID = c.CategoryID
GROUP BY c.CategoryName, p.ProductName
)
SELECT
CategoryName,
ProductName,
TotalSales,
SalesRank
FROM RankedProducts
WHERE SalesRank <= 3
ORDER BY CategoryName, SalesRank;
Cohort Analysis
-- Customer retention by signup month
WITH CustomerCohorts AS (
SELECT
c.CustomerID,
DATEFROMPARTS(YEAR(c.SignupDate), MONTH(c.SignupDate), 1) AS CohortMonth,
DATEFROMPARTS(YEAR(o.OrderDate), MONTH(o.OrderDate), 1) AS OrderMonth,
DATEDIFF(MONTH,
DATEFROMPARTS(YEAR(c.SignupDate), MONTH(c.SignupDate), 1),
DATEFROMPARTS(YEAR(o.OrderDate), MONTH(o.OrderDate), 1)
) AS MonthsSinceSignup
FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID
)
SELECT
CohortMonth,
COUNT(DISTINCT CASE WHEN MonthsSinceSignup = 0 THEN CustomerID END) AS Month0,
COUNT(DISTINCT CASE WHEN MonthsSinceSignup = 1 THEN CustomerID END) AS Month1,
COUNT(DISTINCT CASE WHEN MonthsSinceSignup = 2 THEN CustomerID END) AS Month2,
COUNT(DISTINCT CASE WHEN MonthsSinceSignup = 3 THEN CustomerID END) AS Month3
FROM CustomerCohorts
GROUP BY CohortMonth
ORDER BY CohortMonth;
Percentile Calculations
-- Price percentiles
SELECT
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY Price) OVER () AS Percentile25,
PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY Price) OVER () AS Median,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY Price) OVER () AS Percentile75,
PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY Price) OVER () AS Percentile90,
PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY Price) OVER () AS Percentile95
FROM Products;
Performance Tips for Reporting
1. Use Indexed Views for Complex Aggregations
-- Create indexed view for faster reporting
CREATE VIEW vw_ProductSalesSummary
WITH SCHEMABINDING
AS
SELECT
p.ProductID,
COUNT_BIG(*) AS OrderCount,
SUM(oi.Quantity) AS TotalQuantity,
SUM(oi.Quantity * oi.UnitPrice) AS TotalRevenue
FROM dbo.Products p
INNER JOIN dbo.OrderItems oi ON p.ProductID = oi.ProductID
GROUP BY p.ProductID;
GO
CREATE UNIQUE CLUSTERED INDEX IX_vw_ProductSalesSummary
ON vw_ProductSalesSummary(ProductID);
2. Partition Large Tables
-- Partition by year for better query performance
CREATE PARTITION FUNCTION pf_OrdersByYear (DATETIME)
AS RANGE RIGHT FOR VALUES ('2023-01-01', '2024-01-01', '2025-01-01');
3. Use Appropriate Data Types
-- Use DATE instead of DATETIME for date-only columns
-- Use DECIMAL instead of FLOAT for money
-- Use appropriate string lengths
Conclusion
These reporting techniques enable you to: - Create executive dashboards - Perform trend analysis - Build business intelligence reports - Analyze customer behavior - Track KPIs effectively
Master these patterns and you'll be able to answer any business question with SQL!
Practice these queries with your own data - reporting is where SQL truly shines!