Aggregation and Grouping

9.1 Aggregate Functions

AVG of an INT column returns an INT. Use AVG(CAST(col AS DECIMAL(10,2))) for a decimal result.

SELECT COUNT(*)              AS TotalRows,     -- counts NULLs too
       COUNT(Email)          AS WithEmail,     -- ignores NULLs
       COUNT(DISTINCT City)  AS Cities,
       COUNT_BIG(*)          AS BigCount,
       SUM(UnitPrice), AVG(UnitPrice), MIN(UnitPrice), MAX(UnitPrice),
       STDEV(UnitPrice), STDEVP(UnitPrice), VAR(UnitPrice), VARP(UnitPrice),
       APPROX_COUNT_DISTINCT(ProductName),           -- 2019+
       PRODUCT(1 + Discount)                         -- 2025+
FROM dbo.Products;

9.2 GROUP BY

Every non-aggregated column in SELECT must appear in GROUP BY.

SELECT CategoryID, COUNT(*) AS ProductCount, AVG(UnitPrice) AS AvgPrice
FROM dbo.Products
GROUP BY CategoryID;

SELECT YEAR(OrderDate) AS Yr, MONTH(OrderDate) AS Mn, COUNT(*) AS Orders
FROM dbo.Orders
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY Yr, Mn;

9.3 HAVING

SELECT CustomerID, COUNT(*) AS OrderCount
FROM dbo.Orders
WHERE Status <> 'Cancelled'        -- filters rows before grouping
GROUP BY CustomerID
HAVING COUNT(*) >= 5               -- filters groups after grouping
ORDER BY OrderCount DESC;

9.4 ROLLUP, CUBE, GROUPING SETS

-- Subtotals + grand total (hierarchical)
SELECT Country, City, COUNT(*) FROM dbo.Customers GROUP BY ROLLUP (Country, City);

-- All combinations
SELECT Country, City, COUNT(*) FROM dbo.Customers GROUP BY CUBE (Country, City);

-- Custom groupings
SELECT Country, City, COUNT(*) FROM dbo.Customers
GROUP BY GROUPING SETS ((Country, City), (Country), ());

-- Identify subtotal rows
SELECT CASE WHEN GROUPING(Country) = 1 THEN 'ALL' ELSE Country END AS Country,
       GROUPING_ID(Country, City) AS GrpLevel, COUNT(*)
FROM dbo.Customers GROUP BY ROLLUP (Country, City);

9.5 Statistical Aggregates

SELECT DISTINCT CategoryID,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY UnitPrice) OVER (PARTITION BY CategoryID) AS Median
FROM dbo.Products;

SELECT APPROX_PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY UnitPrice) FROM dbo.Products; -- 2022+