HAVING clause for filtering groups in SQL

Introduction

The HAVING clause helps you filter groups of data after you have grouped 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 average salaries above $50,000.

Syntax

SQL

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

The HAVING clause comes after GROUP BY.

You use aggregate functions like COUNT(), SUM(), AVG() in the HAVING condition.

Examples

This finds departments with more than 3 employees.

SQL

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

This shows products sold in quantities of 100 or more.

SQL

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

This lists cities where the average salary is above $50,000.

SQL

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

Sample Program

This query finds customers who made more than 2 orders and shows how many orders and total amount they spent.

SQL

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, 50.00),
(5, 102, 200.00),
(6, 101, 100.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 must use GROUP BY with HAVING; otherwise, HAVING acts like WHERE but is less efficient.

Summary

HAVING filters groups created by GROUP BY.

Use aggregate functions in HAVING conditions.

HAVING helps answer questions about groups, like totals or averages.