HAVING clause in MySQL

Introduction

The HAVING clause helps you filter groups of data after you group them. It works like a filter but for groups, not individual rows.

When you want to find groups with a total count above a certain number, like customers with more than 5 orders.

When you want to filter groups based on the sum or average of a column, like products with total sales over $1000.

When you want to show only groups that meet a condition after grouping, like departments with more than 10 employees.

When you need to filter grouped results after using GROUP BY in a query.

Syntax

MySQL

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING condition;

The HAVING clause comes after GROUP BY in the query.

You use HAVING to filter groups based on aggregate functions like COUNT(), SUM(), AVG(), etc.

Examples

Shows departments with more than 10 employees.

MySQL

SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

Finds products sold more than 100 units.

MySQL

SELECT product_id, SUM(quantity) AS total_sold
FROM sales
GROUP BY product_id
HAVING SUM(quantity) > 100;

Lists cities where average salary is at least $50,000.

MySQL

SELECT city, AVG(salary) AS avg_salary
FROM employees
GROUP BY city
HAVING AVG(salary) >= 50000;

Sample Program

This query creates a table of orders, inserts some data, then finds customers with more than 2 orders, showing their order count and total amount spent.

MySQL

CREATE TABLE orders (
  order_id INT,
  customer_id INT,
  amount DECIMAL(10,2)
);

INSERT INTO orders VALUES
(1, 101, 250.00),
(2, 102, 150.00),
(3, 101, 300.00),
(4, 103, 200.00),
(5, 101, 100.00),
(6, 102, 50.00);

SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 2;

Important Notes

HAVING filters groups after aggregation, unlike WHERE which filters rows before grouping.

You cannot use column aliases from SELECT directly in HAVING in some databases; use the full aggregate expression instead.

If you don't use GROUP BY, HAVING acts like WHERE but only with aggregate functions.

Summary

HAVING filters grouped data based on conditions.

Use HAVING with GROUP BY to show only groups that meet your criteria.

HAVING works with aggregate functions like COUNT, SUM, AVG.