Tutorials›SQL›HAVING Clause
🟢 Free Demo
SQL TutorialTopic 9 of 20

HAVING Clause

Filter groups after aggregation

✅ What You Will Learn

›How HAVING filters groups after GROUP BY is applied
›The key difference between HAVING and WHERE
›How to use HAVING with COUNT, SUM, AVG, MIN, MAX
›How to combine WHERE and HAVING in one query

HAVING filters groups created by GROUP BY — the same way WHERE filters individual rows. You cannot use WHERE to filter on aggregate results like SUM or COUNT because WHERE runs before grouping happens. HAVING runs after.

A simple rule: use WHERE to filter rows before grouping, use HAVING to filter groups after grouping.

📋 The orders table used in examples

order_idproductamountcity
1001Laptop45000Delhi
1002Mobile Phone18000Noida
1003Headphones3500Gurgaon
1004Laptop52000Delhi
1005Tablet28000Noida
1006Laptop48000Delhi

Syntax

SQL SYNTAX
SELECT column, AGGREGATE_FUNCTION(column)
FROM table_name
GROUP BY column
HAVING condition_on_aggregate;

Examples

Example 1Products with total revenue above ₹10,000
SELECT product, SUM(amount) AS total_revenue
FROM orders
GROUP BY product
HAVING SUM(amount) > 10000;
OUTPUT
product      | total_revenue
-------------|-------------
Laptop       | 97000
Mobile Phone | 18000
💡

Headphones (₹3,500) is excluded because its total does not meet the HAVING condition.

Example 2Products ordered more than once
SELECT product, COUNT(*) AS order_count
FROM orders
GROUP BY product
HAVING COUNT(*) > 1;
OUTPUT
product | order_count
--------|------------
Laptop  | 2
Example 3WHERE + GROUP BY + HAVING together
SELECT product,
       COUNT(*) AS order_count,
       SUM(amount) AS total_revenue
FROM orders
WHERE order_date >= '2026-01-16'     -- filter rows first
GROUP BY product                      -- then group
HAVING SUM(amount) > 5000            -- then filter groups
ORDER BY total_revenue DESC;
💡

Execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. This order determines where each clause fits.

📌 Key Points to Remember

  • ✓HAVING filters groups — WHERE filters rows
  • ✓HAVING goes after GROUP BY, before ORDER BY
  • ✓You can use any aggregate function in HAVING (SUM, COUNT, AVG, etc.)
  • ✓You can combine WHERE and HAVING in the same query
  • ✓SQL execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

🏢 Real-World Application

HAVING is essential for any report that filters based on aggregate totals. "Show only product categories with more than 100 orders this month" — HAVING COUNT(*) > 100. "Find salespeople whose monthly target exceeds ₹5 lakh" — HAVING SUM(sales) > 500000. "Show only cities where average order value is above ₹2,000" — HAVING AVG(order_value) > 2000. These are standard business reporting queries. The combination of GROUP BY and HAVING is the core of SQL-based KPI reporting.

⚠️ Common Mistakes to Avoid

WRONGUsing WHERE instead of HAVING for aggregate filters
FIXWHERE SUM(amount) > 10000 is invalid — WHERE runs before aggregation and cannot use aggregate functions. Move aggregate conditions to HAVING: HAVING SUM(amount) > 10000.
WRONGPutting HAVING before GROUP BY
FIXThe correct order is GROUP BY first, then HAVING. HAVING placed before GROUP BY causes a syntax error.
WRONGUsing HAVING when WHERE is more efficient
FIXIf the filter does not involve an aggregate function, use WHERE instead of HAVING. WHERE filters rows before grouping (fewer rows to group = faster query). HAVING filters after grouping.
✏️Test Yourself

Which clause filters groups after GROUP BY?

❓ Frequently Asked Questions

What is HAVING in SQL?

HAVING filters the results of a GROUP BY query. It works like WHERE but applies after grouping and can use aggregate functions like SUM(), COUNT(), AVG(). Example: HAVING COUNT(*) > 5 keeps only groups with more than 5 rows.

Can I use HAVING without GROUP BY?

Technically yes — HAVING without GROUP BY treats the entire result as one group. But this is rarely useful. HAVING is almost always used with GROUP BY.

Can I use both WHERE and HAVING in the same query?

Yes. WHERE filters rows before grouping; HAVING filters groups after grouping. SELECT product, SUM(amount) FROM orders WHERE order_date > '2026-01-01' GROUP BY product HAVING SUM(amount) > 50000 uses both.

What aggregate functions can I use with HAVING?

All standard aggregate functions work with HAVING: COUNT(), SUM(), AVG(), MIN(), MAX(). You can also use expressions like HAVING SUM(amount) / COUNT(*) > 1000.

What is the order of SQL clauses when using GROUP BY and HAVING?

The correct order is: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT. Each clause must appear in this sequence or you will get a syntax error.

✏️ Practice Exercise

Write a query to find all products where the average order amount is above ₹15,000.

← PreviousGROUP BYNext →SQL Joins — Introduction
🎓 Level Up Faster

Learn SQL with Live Trainer Guidance

These tutorials give you the theory. Our live SQL course at EVIKA Academy, Noida teaches you to apply SQL on real company datasets — with a trainer who uses it daily at MakeMyTrip.