SQL HAVING Syntax
SELECT column, AGGREGATE(column)
FROM table_name
WHERE row_condition
GROUP BY column
HAVING aggregate_condition
ORDER BY column;HAVING is always used after GROUP BY. It works with aggregate functions like COUNT, SUM, AVG, MIN, and MAX.
Sample Table
+----+-----------+------------+--------+-----------+
| ID | Name | Department | Salary | City |
+----+-----------+------------+--------+-----------+
| 1 | Priya K. | Sales | 55000 | Mumbai |
| 2 | John D. | Marketing | 62000 | London |
| 3 | Arun M. | IT | 78000 | Delhi |
| 4 | Emma W. | Sales | 51000 | Sydney |
| 5 | Ravi S. | IT | 72000 | Mumbai |
| 6 | Sara L. | HR | 58000 | New York |
+----+-----------+------------+--------+-----------+HAVING with COUNT
Find departments that have more than 1 employee:
SELECT Department, COUNT(*) AS EmpCount
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 1;+------------+----------+ | Department | EmpCount | +------------+----------+ | IT | 2 | | Sales | 2 | +------------+----------+
HR and Marketing are excluded because they each have only 1 employee.
HAVING with SUM
Find departments where the total salary exceeds 100,000:
SELECT Department, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department
HAVING SUM(Salary) > 100000;+------------+-------------+ | Department | TotalSalary | +------------+-------------+ | IT | 150000 | | Sales | 106000 | +------------+-------------+
HAVING vs WHERE
This is one of the most important distinctions in SQL:
| Feature | WHERE | HAVING |
|---|---|---|
| Filters | Individual rows | Groups (after aggregation) |
| Runs | Before GROUP BY | After GROUP BY |
| Aggregates | Cannot use aggregate functions | Must use aggregate functions |
| Example | WHERE Salary > 50000 |
HAVING COUNT(*) > 1 |
Using WHERE and HAVING Together
You can combine both — WHERE filters rows first, then HAVING filters the resulting groups:
SELECT Department,
COUNT(*) AS EmpCount,
AVG(Salary) AS AvgSalary
FROM Employees
WHERE Salary > 50000
GROUP BY Department
HAVING COUNT(*) > 1
ORDER BY AvgSalary DESC;+------------+----------+-----------+ | Department | EmpCount | AvgSalary | +------------+----------+-----------+ | IT | 2 | 75000 | | Sales | 1 | 55000 | +------------+----------+-----------+
Step by step: (1) WHERE removes Emma W. (Salary 51000), (2) GROUP BY groups the remaining rows, (3) HAVING keeps only groups with more than 1 employee.
⚠️ Common Mistake: Using WHERE Instead of HAVING
WHERE COUNT(*) > 1 will cause an error because WHERE runs before aggregation. Aggregate conditions must always go in HAVING, not WHERE.
HAVING with AVG
Find departments where the average salary is above 60,000:
SELECT Department,
ROUND(AVG(Salary), 0) AS AvgSalary
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > 60000;+------------+-----------+ | Department | AvgSalary | +------------+-----------+ | IT | 75000 | | Marketing | 62000 | +------------+-----------+
For the complete HAVING reference, see the official MySQL GROUP BY documentation. In the next chapter, you will learn SQL UNION to combine results from multiple queries.
If you work with data regularly, explore our ready-made Dashboard Templates on NextGenTemplates that use SQL-powered data for business reporting.
📺 Visit our YouTube channel @NeoTechNavigators for step-by-step video tutorials and dashboard demos on this topic.
🧪 Try It Yourself
Write a query that shows each City with its employee count and total salary, but only for cities with more than 1 employee.
SELECT City,
COUNT(*) AS EmpCount,
SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY City
HAVING COUNT(*) > 1;Expected Output:
+-----------+----------+-------------+ | City | EmpCount | TotalSalary | +-----------+----------+-------------+ | Mumbai | 2 | 127000 | +-----------+----------+-------------+
📝 What You Learned in This Chapter
HAVINGfilters groups after aggregation- WHERE filters rows before grouping — HAVING filters groups after
- HAVING always comes after GROUP BY
- HAVING works with aggregate functions: COUNT, SUM, AVG, MIN, MAX
- You can combine WHERE and HAVING in the same query
WHERE COUNT(*) > 1is invalid — aggregate conditions belong in HAVING
Frequently Asked Questions
HAVING COUNT(*) > 2 keeps only groups with more than 2 rows.WHERE filters individual rows before grouping. HAVING filters groups after aggregation. WHERE cannot use aggregate functions (COUNT, SUM, etc.), while HAVING is designed specifically for aggregate conditions.SELECT COUNT(*) FROM Employees HAVING COUNT(*) > 3. However, this is uncommon — HAVING is almost always used with GROUP BY.WHERE Salary > 50000 GROUP BY Department HAVING COUNT(*) > 1. WHERE runs first, then GROUP BY, then HAVING.WHERE runs before GROUP BY, so aggregate functions like COUNT are not yet calculated. Aggregate conditions must go in HAVING, which runs after GROUP BY when aggregates are available.📖 Chapter 26 of 30 — SQL Tutorial on Neotech Navigators
