What is the HAVING Clause?
The HAVING clause is used to filter groups created by the
GROUP BY clause. Unlike WHERE, which filters
individual rows before grouping, HAVING filters the
resulting groups after aggregate calculations are performed.
Basic HAVING Syntax
HAVING is commonly used together with GROUP BY and an aggregate function.
DROP TABLE IF EXISTS sales;
CREATE TABLE sales (
sale_id INTEGER,
category TEXT,
amount INTEGER
);
INSERT INTO sales VALUES
(1, 'Books', 500),
(2, 'Books', 700),
(3, 'Games', 1200),
(4, 'Games', 900),
(5, 'Clothing', 400);
SELECT
category,
SUM(amount) AS total_sales
FROM sales
GROUP BY category
HAVING SUM(amount) > 1000
ORDER BY total_sales DESC;HAVING with COUNT()
You can use HAVING with COUNT() to return only
groups containing a specific number of rows.
DROP TABLE IF EXISTS enrollments;
CREATE TABLE enrollments (
enrollment_id INTEGER,
course TEXT,
student_name TEXT
);
INSERT INTO enrollments VALUES
(1, 'SQL', 'Aarav'),
(2, 'SQL', 'Meera'),
(3, 'SQL', 'Kabir'),
(4, 'Python', 'Riya'),
(5, 'Python', 'Dev'),
(6, 'HTML', 'Tara');
SELECT
course,
COUNT(*) AS student_count
FROM enrollments
GROUP BY course
HAVING COUNT(*) >= 2
ORDER BY student_count DESC;HAVING with AVG()
The AVG() function can be used with HAVING to filter groups
based on their average value.
DROP TABLE IF EXISTS employee_scores;
CREATE TABLE employee_scores (
employee_id INTEGER,
department TEXT,
score INTEGER
);
INSERT INTO employee_scores VALUES
(1, 'Design', 78),
(2, 'Design', 84),
(3, 'Engineering', 92),
(4, 'Engineering', 88),
(5, 'Marketing', 65),
(6, 'Marketing', 72);
SELECT
department,
ROUND(AVG(score), 2) AS average_score
FROM employee_scores
GROUP BY department
HAVING AVG(score) >= 80
ORDER BY average_score DESC;HAVING with MIN()
HAVING can filter groups according to their smallest value using
MIN().
DROP TABLE IF EXISTS product_prices;
CREATE TABLE product_prices (
product_id INTEGER,
category TEXT,
price INTEGER
);
INSERT INTO product_prices VALUES
(1, 'Laptop', 55000),
(2, 'Laptop', 62000),
(3, 'Phone', 18000),
(4, 'Phone', 24000),
(5, 'Tablet', 12000),
(6, 'Tablet', 15000);
SELECT
category,
MIN(price) AS lowest_price
FROM product_prices
GROUP BY category
HAVING MIN(price) >= 15000
ORDER BY lowest_price DESC;HAVING with MAX()
The MAX() function can be used with HAVING to keep only
groups whose maximum value meets a specified condition.
DROP TABLE IF EXISTS temperatures;
CREATE TABLE temperatures (
reading_id INTEGER,
city TEXT,
temperature INTEGER
);
INSERT INTO temperatures VALUES
(1, 'Delhi', 34),
(2, 'Delhi', 39),
(3, 'Pune', 28),
(4, 'Pune', 32),
(5, 'Jaipur', 36),
(6, 'Jaipur', 41);
SELECT
city,
MAX(temperature) AS highest_temperature
FROM temperatures
GROUP BY city
HAVING MAX(temperature) > 38
ORDER BY highest_temperature DESC;HAVING with Multiple Conditions
Multiple conditions can be combined inside HAVING using operators such
as AND and OR.
DROP TABLE IF EXISTS store_orders;
CREATE TABLE store_orders (
order_id INTEGER,
store TEXT,
amount INTEGER
);
INSERT INTO store_orders VALUES
(1, 'Central', 900),
(2, 'Central', 1200),
(3, 'Central', 700),
(4, 'West', 500),
(5, 'West', 600),
(6, 'North', 1500),
(7, 'North', 1300);
SELECT
store,
COUNT(*) AS order_count,
SUM(amount) AS total_sales
FROM store_orders
GROUP BY store
HAVING COUNT(*) >= 2
AND SUM(amount) > 2000
ORDER BY total_sales DESC;HAVING with WHERE
WHERE filters individual rows before grouping, while HAVING filters the groups after aggregation.
DROP TABLE IF EXISTS transactions;
CREATE TABLE transactions (
transaction_id INTEGER,
department TEXT,
amount INTEGER
);
INSERT INTO transactions VALUES
(1, 'Books', 300),
(2, 'Books', 800),
(3, 'Books', 1500),
(4, 'Games', 400),
(5, 'Games', 1100),
(6, 'Games', 1800),
(7, 'Clothing', 900);
SELECT
department,
SUM(amount) AS total_sales
FROM transactions
WHERE amount >= 500
GROUP BY department
HAVING SUM(amount) > 2000
ORDER BY total_sales DESC;HAVING with Multiple Columns
HAVING can be used after grouping by multiple columns to filter each unique combination of grouped values.
DROP TABLE IF EXISTS regional_sales;
CREATE TABLE regional_sales (
sale_id INTEGER,
region TEXT,
category TEXT,
amount INTEGER
);
INSERT INTO regional_sales VALUES
(1, 'North', 'Books', 800),
(2, 'North', 'Books', 700),
(3, 'North', 'Games', 500),
(4, 'South', 'Books', 1200),
(5, 'South', 'Books', 900),
(6, 'South', 'Games', 400),
(7, 'West', 'Games', 1400),
(8, 'West', 'Games', 900);
SELECT
region,
category,
SUM(amount) AS total_sales
FROM regional_sales
GROUP BY region, category
HAVING SUM(amount) >= 1400
ORDER BY total_sales DESC;HAVING with SUM()
SUM() is frequently combined with HAVING when you need to find groups whose total value is above or below a particular amount.
DROP TABLE IF EXISTS monthly_revenue;
CREATE TABLE monthly_revenue (
month_name TEXT,
channel TEXT,
revenue INTEGER
);
INSERT INTO monthly_revenue VALUES
('January', 'Online', 1800),
('January', 'Store', 1200),
('February', 'Online', 2400),
('February', 'Store', 900),
('March', 'Online', 1500),
('March', 'Store', 1700);
SELECT
month_name,
SUM(revenue) AS total_revenue
FROM monthly_revenue
GROUP BY month_name
HAVING SUM(revenue) >= 3000
ORDER BY total_revenue DESC;HAVING with COUNT(DISTINCT)
In SQLite, COUNT(DISTINCT column) can be used with HAVING
to filter groups based on the number of unique values.
DROP TABLE IF EXISTS customer_orders;
CREATE TABLE customer_orders (
order_id INTEGER,
salesperson TEXT,
customer TEXT
);
INSERT INTO customer_orders VALUES
(1, 'Neha', 'Aarav'),
(2, 'Neha', 'Meera'),
(3, 'Neha', 'Aarav'),
(4, 'Rohan', 'Kabir'),
(5, 'Rohan', 'Tara'),
(6, 'Rohan', 'Dev');
SELECT
salesperson,
COUNT(DISTINCT customer) AS unique_customers
FROM customer_orders
GROUP BY salesperson
HAVING COUNT(DISTINCT customer) >= 2
ORDER BY unique_customers DESC;HAVING with ORDER BY
After HAVING filters the groups, ORDER BY can sort the remaining results.
DROP TABLE IF EXISTS department_expenses;
CREATE TABLE department_expenses (
expense_id INTEGER,
department TEXT,
amount INTEGER
);
INSERT INTO department_expenses VALUES
(1, 'Design', 700),
(2, 'Design', 900),
(3, 'Engineering', 1500),
(4, 'Engineering', 1800),
(5, 'Marketing', 600),
(6, 'Marketing', 800);
SELECT
department,
SUM(amount) AS total_expense
FROM department_expenses
GROUP BY department
HAVING SUM(amount) >= 1500
ORDER BY total_expense DESC;WHERE vs HAVING
| Feature | WHERE | HAVING |
|---|---|---|
| Filters | Individual rows | Groups |
| Execution | Before GROUP BY | After GROUP BY |
| Aggregate functions | Usually not used | Commonly used |
| Example | WHERE amount > 500 | HAVING SUM(amount) > 2000 |
Common Uses of HAVING
- Find categories with sales above a specific amount.
- Find departments containing a minimum number of employees.
- Find products whose average price meets a condition.
- Filter groups based on minimum or maximum values.
- Analyze summarized data after GROUP BY.
Best Practices
- Use HAVING when you need to filter grouped or aggregated results.
- Use WHERE to filter individual rows before grouping.
- Use aggregate functions such as COUNT(), SUM(), and AVG() with HAVING when appropriate.
- Use ORDER BY after HAVING when the final groups need to be sorted.
- Use meaningful aliases for calculated values.
- For repeatable SQLite examples, use
DROP TABLE IF EXISTSbefore creating tutorial tables.
The HAVING clause is used to filter
groups after SQL performs grouping and aggregation. It is especially
useful with COUNT(), SUM(),
AVG(), MIN(), and MAX().
🧪 Test Your SQL Code
Edit the SQL code on the left and click “Run Code” to see the result on the right.
Click “Run Code” to see the result here.