HAVING Clause

To follow along with the examples in this lesson, use the course dataset from Lesson 8.

What HAVING Does

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 execution 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.

• HAVING can only appear after GROUP BY — it has no meaning without grouping
• HAVING conditions reference aggregate results — HAVING COUNT(*) > 5, HAVING SUM(total_amount) > 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
• Aliases from SELECT cannot be used in HAVING — the full aggregate expression must be repeated
• HAVING behaves identically in Oracle and SQL Server — no syntax differences

-- Show only categories that have more than 5 products.
-- WHERE cannot do this -- the count does not exist yet at the WHERE stage.
SELECT
    category,
    COUNT(*)             AS total_products,
    ROUND(AVG(price), 2) AS avg_price
FROM   dataplexa_store.products
GROUP  BY category
HAVING COUNT(*) > 5          -- filters groups after aggregation -- not individual rows
ORDER  BY total_products DESC;

• Clothing had exactly 5 products and was excluded — 5 > 5 is false
• COUNT(*) must be repeated in HAVING — writing HAVING total_products > 5 causes ORA-00904 because aliases are not resolved at the HAVING stage
• ORDER BY total_products DESC can use the alias because ORDER BY runs after HAVING — aliases are already resolved by then

HAVING with SUM and AVG

HAVING works with any aggregate function — not just COUNT. Use HAVING SUM(total_amount) > 10000 to find high-revenue groups, HAVING AVG(price) > 100 to find premium categories, or HAVING MAX(salary) > 90000 to find departments with senior earners. The logic is always the same: calculate the aggregate per group, then test each group's result against the condition.

-- Find departments where total payroll exceeds 500,000.
SELECT
    d.department_name,
    COUNT(e.employee_id)      AS headcount,
    SUM(e.salary)             AS total_payroll,
    ROUND(AVG(e.salary), 2)   AS avg_salary
FROM   dataplexa_hr.employees   e
JOIN   dataplexa_hr.departments d ON e.department_id = d.department_id
GROUP  BY d.department_name
HAVING SUM(e.salary) > 500000   -- only departments whose combined salary exceeds this threshold
ORDER  BY total_payroll DESC;

• Finance, Warehouse and HR all fell below the 500,000 threshold and were removed as entire groups
• SUM(e.salary) is computed per department group before the HAVING check runs — the JOIN and GROUP BY both execute first
• 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 rows that do not match — those rows never enter the grouping step at all. HAVING runs after grouping and removes entire groups that fail the aggregate condition. Using both gives you precise control: narrow the raw data with WHERE first, then filter the summaries with HAVING.

-- Which Electronics brands have an average price above 50
-- AND at least 3 products?
-- WHERE restricts to Electronics rows only -- non-Electronics rows never reach GROUP BY.
-- HAVING then filters the brand groups by aggregate conditions.
SELECT
    brand,
    COUNT(*)             AS products,
    ROUND(AVG(price), 2) AS avg_price,
    MIN(price)           AS min_price,
    MAX(price)           AS max_price
FROM   dataplexa_store.products
WHERE  category = 'Electronics'   -- WHERE: only Electronics rows enter the grouping step
GROUP  BY brand
HAVING COUNT(*) >= 3              -- HAVING: remove brands with fewer than 3 products
   AND AVG(price) > 50            -- HAVING: and remove brands whose average price is 50 or below
ORDER  BY avg_price DESC;

• WHERE removed all non-Electronics rows before grouping — Furniture, Stationery, and Clothing never existed in this query
• TechLink had 4 products but an average price of $28.24 — excluded by HAVING AVG(price) > 50
• SoundWave had an average above $50 but only 2 products — excluded by HAVING COUNT(*) >= 3
• HAVING can combine multiple conditions with AND and OR — the same logic operators as WHERE

HAVING with BETWEEN and Multiple Conditions

HAVING supports the same comparison operators as WHERE. You can use BETWEEN to filter groups within a range, combine multiple aggregate conditions with AND or OR, and mix both to build precise filters. The condition just needs to evaluate to true or false for each group after aggregation.

-- Categories where average price falls between 20 and 200
-- and total stock is above 300 units.
SELECT
    category,
    COUNT(*)             AS total_products,
    ROUND(AVG(price), 2) AS avg_price,
    SUM(stock)           AS total_stock
FROM   dataplexa_store.products
GROUP  BY category
HAVING AVG(price) BETWEEN 20 AND 200   -- BETWEEN works identically in Oracle and SQL Server
   AND SUM(stock) > 300
ORDER  BY avg_price DESC;

• BETWEEN 20 AND 200 in HAVING works identically in Oracle and SQL Server — inclusive on both ends
• Furniture was excluded because its average price of 289.99 exceeds the upper bound of 200
• Stationery was excluded because its average price of 14.75 falls below the lower bound of 20

-- Monthly revenue report -- only months with revenue above 5,000
-- and more than 40 orders placed.
SELECT
    TO_CHAR(order_date, 'YYYY-MM') AS order_month,
    COUNT(*)          AS total_orders,
    SUM(total_amount) AS revenue
FROM   dataplexa_store.orders
WHERE  order_date >= DATE '2024-01-01'
GROUP  BY TO_CHAR(order_date, 'YYYY-MM')   -- full expression repeated -- alias not allowed in GROUP BY
HAVING SUM(total_amount) > 5000
   AND COUNT(*) > 40
ORDER  BY order_month;

• January had only 38 orders — excluded by HAVING COUNT(*) > 40
• February had 41 orders but both conditions must be true — it passed COUNT but its revenue was above 5,000 so it was included as well
• TO_CHAR() expression must be repeated in GROUP BY — alias order_month is not allowed there

Oracle vs SQL Server — HAVING Differences

Lesson Summary

Practice Questions

Quiz

Next up — NULL Handling — What NULL means in Oracle, how it behaves in comparisons and aggregates, and how NVL, NVL2, and NULLIF replace SQL Server's ISNULL and COALESCE.