GROUP BY and HAVING in SQL Server

GROUP BY collapses rows that share common values into summary rows, while aggregate functions like COUNT, SUM, AVG, MIN, and MAX compute values across those groups. The HAVING clause then filters the grouped results. Together, they form the backbone of data analysis in SQL Server.

Basic GROUP BY

Every non-aggregated column in your SELECT must appear in the GROUP BY clause. The aggregate functions operate on each group independently.

SELECT
    Department,
    COUNT(*) AS EmployeeCount,
    AVG(Salary) AS AvgSalary,
    MAX(Salary) AS MaxSalary
FROM Employees
GROUP BY Department
ORDER BY EmployeeCount DESC;

Grouping by Multiple Columns

You can group by multiple columns to create more granular summaries. Each unique combination of values becomes its own group.

SELECT
    YEAR(OrderDate) AS OrderYear,
    MONTH(OrderDate) AS OrderMonth,
    COUNT(*) AS TotalOrders,
    SUM(OrderTotal) AS Revenue
FROM Orders
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY OrderYear, OrderMonth;

HAVING: Filtering Groups

HAVING filters groups after aggregation, while WHERE filters individual rows before grouping. Use HAVING when your condition involves an aggregate function.

-- Departments with more than 10 employees
SELECT
    Department,
    COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 10;

-- Customers who spent more than $5000
SELECT
    c.CustomerName,
    SUM(o.OrderTotal) AS TotalSpent
FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName
HAVING SUM(o.OrderTotal) > 5000
ORDER BY TotalSpent DESC;

WHERE vs HAVING

A common mistake is using HAVING when WHERE would be more efficient. Filter individual rows with WHERE first to reduce the data before grouping.

-- Correct: WHERE filters rows, HAVING filters groups
SELECT
    Category,
    COUNT(*) AS ProductCount,
    AVG(Price) AS AvgPrice
FROM Products
WHERE IsActive = 1              -- filter rows first
GROUP BY Category
HAVING AVG(Price) > 25.00       -- then filter groups
ORDER BY AvgPrice DESC;

GROUPING SETS, ROLLUP, and CUBE

SQL Server extends GROUP BY with ROLLUP for subtotals and grand totals, and CUBE for all possible grouping combinations.

-- ROLLUP adds subtotals and grand total
SELECT
    COALESCE(Department, 'TOTAL') AS Department,
    COALESCE(CAST(YEAR(HireDate) AS VARCHAR), 'All Years') AS HireYear,
    COUNT(*) AS Headcount
FROM Employees
GROUP BY ROLLUP(Department, YEAR(HireDate));

Mastering GROUP BY and HAVING allows you to transform raw data into actionable summaries. Combine these with JOINs and subqueries to build complex analytical reports directly in SQL Server.