HAVING Clause

What Is HAVING?

HAVING filters groups after aggregation — it is the GROUP BY equivalent of WHERE. WHERE filters individual rows before groups are formed. HAVING filters the resulting groups after COUNT(), SUM(), AVG(), MIN(), or MAX() has already been calculated. This is the critical difference: you can reference aggregate results inside a HAVING condition, but you cannot do that inside WHERE.

The full clause order is: WHERE → GROUP BY → HAVING → ORDER BY. WHERE removes rows before grouping. GROUP BY collapses the remaining rows into groups. HAVING removes groups whose aggregate result fails the condition. ORDER BY sorts what is left. Skipping any of these steps does not break the query — but placing a condition in the wrong clause causes either an error or silent wrong results.

• HAVING can only appear after GROUP BY — it has no meaning without grouping
• HAVING conditions reference aggregate results: HAVING COUNT(*) > 5, HAVING SUM(Total) > 1000
• WHERE conditions reference raw column values: WHERE Category = 'Electronics'
• Both can appear in the same query — WHERE pre-filters rows, HAVING post-filters groups
• You can use column aliases from SELECT in ORDER BY, but not in HAVING — repeat the aggregate expression

-- HAVING filters groups. WHERE filters rows. This is the fundamental difference.
-- Here: only show categories that have MORE than 5 products.
-- WHERE cannot do this because the count doesn't exist yet at the WHERE stage.
SELECT
    Category,
    COUNT(*)   AS TotalProducts,  -- aggregate: count rows per group
    AVG(Price) AS AvgPrice        -- aggregate: average price per group
FROM Products
GROUP BY Category                 -- form one group per category
HAVING COUNT(*) > 5               -- filter: only groups where count exceeds 5
ORDER BY TotalProducts DESC;      -- sort the surviving groups

• HAVING COUNT(*) > 5 removed Clothing (5 products) from the result — 5 is not greater than 5
• The aggregate runs first across all groups, then HAVING checks each group's result
• You must repeat COUNT(*) in the HAVING clause — you cannot write HAVING TotalProducts > 5 because aliases are not yet resolved at that stage
• ORDER BY TotalProducts DESC can use the alias because ORDER BY runs after HAVING

HAVING with SUM and AVG

HAVING is not limited to COUNT — any aggregate function works. Use HAVING SUM(Total) > 10000 to find high-revenue groups, HAVING AVG(Price) > 100 to find premium categories, or HAVING MAX(Price) > 500 to find categories containing expensive products. The logic is always the same: calculate the aggregate per group, then test each group's result against the condition.

-- Find countries where total revenue exceeded $5,000.
-- SUM(Total) is calculated per country group, then HAVING filters.
SELECT
    u.Country,
    COUNT(DISTINCT o.UserId)  AS UniqueCustomers,
    COUNT(*)                  AS TotalOrders,
    SUM(o.Total)              AS Revenue,
    ROUND(AVG(o.Total), 2)    AS AvgOrder
FROM Orders  o
JOIN Users   u ON o.UserId = u.UserId
GROUP BY u.Country
HAVING SUM(o.Total) > 5000         -- only countries with more than $5,000 revenue
ORDER BY Revenue DESC;

• Australia ($4,210.00) and France ($3,480.60) are excluded because their revenue is below $5,000
• SUM(o.Total) is computed per country group before the HAVING check runs
• The JOIN runs first, then GROUP BY collapses the joined rows, then HAVING filters the groups
• You can verify a HAVING filter is working by temporarily removing it and comparing the full result to the filtered one

WHERE and HAVING Together

WHERE and HAVING serve different purposes and can both appear in the same query. WHERE runs first and removes individual rows that do not match — these rows never enter the grouping step at all. HAVING runs after grouping and removes entire groups that do not meet the aggregate condition. Using both together gives you precise control: narrow the raw data first with WHERE, then filter the summaries with HAVING.

-- WHERE pre-filters rows. HAVING post-filters groups.
-- Both in the same query for precise control.
-- Which Electronics brands have an average price above $50
-- AND at least 3 products?
SELECT
    Brand,
    COUNT(*)             AS Products,   -- product count for this brand
    ROUND(AVG(Price), 2) AS AvgPrice,   -- average price across the brand
    MIN(Price)           AS MinPrice,   -- cheapest product
    MAX(Price)           AS MaxPrice    -- most expensive product
FROM Products
WHERE Category = 'Electronics'    -- WHERE: only Electronics rows enter the group
GROUP BY Brand                    -- group by brand within that category
HAVING COUNT(*) >= 3              -- HAVING: only brands with 3 or more products
   AND AVG(Price) > 50            -- HAVING: and average price above $50
ORDER BY AvgPrice DESC;

• WHERE removed all non-Electronics rows before any grouping happened — Furniture, Stationery, etc. never existed in this query
• TechLink (avg price $28.24) was excluded by HAVING AVG(Price) > 50 even though it had 4 products
• SoundWave was excluded by HAVING COUNT(*) >= 3 even though its average price exceeded $50
• HAVING can combine multiple conditions with AND and OR — the same logic operators as WHERE

HAVING with BETWEEN, IN, and LIKE

HAVING supports the same comparison operators as WHERE — not just greater-than and less-than. You can use BETWEEN to filter groups within a range, IN to match specific aggregate values, and even wrap the aggregate in a function before comparing. The condition just needs to evaluate to true or false for each group.

-- HAVING with BETWEEN -- only categories whose average price falls in a range.
-- Useful for targeting mid-tier segments without arbitrary single thresholds.
SELECT
    Category,
    COUNT(*)             AS Products,
    ROUND(AVG(Price), 2) AS AvgPrice,
    SUM(Stock)           AS TotalStock
FROM Products
GROUP BY Category
HAVING AVG(Price) BETWEEN 20 AND 150  -- only categories with avg price in this band
ORDER BY AvgPrice DESC;
-- HAVING with a calculated expression -- only categories where
-- total stock value (stock × avg price) exceeds $50,000
SELECT
    Category,
    SUM(Stock)                         AS TotalUnits,
    ROUND(AVG(Price), 2)               AS AvgPrice,
    ROUND(SUM(Stock * Price), 2)       AS StockValue  -- total value of inventory
FROM Products
GROUP BY Category
HAVING SUM(Stock * Price) > 50000      -- groups where inventory value exceeds $50k
ORDER BY StockValue DESC;

• HAVING AVG(Price) BETWEEN 20 AND 150 uses the same BETWEEN syntax as WHERE — inclusive on both ends
• SUM(Stock * Price) computes the total inventory value per category — the multiplication happens row by row, then SUM accumulates the result per group
• Furniture was excluded from the BETWEEN query because its average price of $289.99 exceeded the upper bound of $150
• Any expression that produces a number, date, or string can be used inside a HAVING aggregate

HAVING vs WHERE — The Decision Rule

The question of whether to use WHERE or HAVING comes down to one thing: are you filtering on raw column data, or on the result of an aggregate calculation? If the value you are comparing against already exists in a column, use WHERE. If it only exists after the engine has calculated a COUNT, SUM, AVG, MIN, or MAX across a group, use HAVING. Using WHERE when you need HAVING causes an error. Using HAVING when you should use WHERE wastes work — the engine groups all rows first, then discards most of them, rather than discarding them upfront.

-- Side-by-side: wrong approach vs correct approach for each filter type.
-- WRONG: trying to use WHERE on an aggregate result
-- SQL Server returns: "Invalid use of aggregate function in WHERE clause"
SELECT Category, COUNT(*) AS Total
FROM Products
WHERE COUNT(*) > 5        -- ERROR: COUNT() cannot appear in WHERE
GROUP BY Category;
-- CORRECT: move the aggregate filter to HAVING
SELECT Category, COUNT(*) AS Total
FROM Products
GROUP BY Category
HAVING COUNT(*) > 5;      -- aggregate filters belong in HAVING
-- WRONG: using HAVING for a plain column filter (legal but inefficient)
SELECT Category, COUNT(*) AS Total
FROM Products
GROUP BY Category
HAVING Category = 'Electronics';  -- works, but groups ALL categories first then discards
-- CORRECT: filter raw column data in WHERE before grouping
SELECT Category, COUNT(*) AS Total
FROM Products
WHERE Category = 'Electronics'    -- WHERE discards non-Electronics rows immediately
GROUP BY Category;                -- only one group ever forms

• Using an aggregate function inside WHERE causes an immediate error in SQL Server — it is simply not permitted
• Using HAVING for a plain column filter is legal but forces the engine to build all groups before discarding most of them
• The correct pattern: filter raw columns with WHERE first, filter aggregate results with HAVING after
• When both appear, WHERE always runs before GROUP BY, HAVING always runs after — this order is fixed by SQL Server

HAVING with GROUP BY on Multiple Columns

HAVING works exactly the same way when you are grouping by multiple columns — it filters on the aggregate result of each unique combination. A group must satisfy the HAVING condition as a whole combination, not just by one of its grouping columns individually.

-- Monthly revenue summary for 2024 -- only months that exceeded $4,500 in revenue.
-- Grouped by year+month combination. HAVING filters the monthly totals.
SELECT
    YEAR(OrderDate)      AS OrderYear,
    MONTH(OrderDate)     AS OrderMonth,
    COUNT(*)             AS Orders,
    SUM(Total)           AS Revenue,
    ROUND(AVG(Total), 2) AS AvgOrder
FROM Orders
WHERE YEAR(OrderDate) = 2024          -- WHERE: only 2024 rows enter the group
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
HAVING SUM(Total) > 4500              -- HAVING: only months with revenue above $4,500
ORDER BY OrderYear, OrderMonth;

• Three months qualified — January, March, and June exceeded $4,500 in revenue
• February, April, and May were removed as complete groups — all their rows were already inside the groups, just the groups themselves failed the HAVING test
• WHERE ran first and removed all non-2024 rows, then GROUP BY formed monthly groups, then HAVING removed low-revenue months
• This is the standard pattern for time-series filtering — pre-filter by year in WHERE, post-filter by monthly threshold in HAVING

Lesson Summary

Practice Questions

Quiz

Next up — NULL Handling - What NULL means in SQL Server, how it behaves in comparisons and aggregates, and how to test for it correctly with IS NULL and IS NOT NULL.