HAVING for filtering groups in PostgreSQL

Introduction

HAVING lets you filter groups of data after you group them, so you only see groups that meet certain conditions.

You want to find customers who bought more than 5 items in total.

You want to list products with total sales above a certain amount.

You want to see which cities have more than 10 stores.

You want to filter groups after counting or summing values.

Syntax

PostgreSQL

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

HAVING works like WHERE but for groups, not individual rows.

You must use GROUP BY before HAVING.

Examples

Find departments with more than 3 employees.

PostgreSQL

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

Show products sold more than 100 units.

PostgreSQL

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

List cities where average salary is above 50,000.

PostgreSQL

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

Sample Program

This query creates a sales table, inserts some data, then finds products with total quantity sold over 20.

PostgreSQL

CREATE TABLE sales (
  product_id INT,
  quantity INT
);

INSERT INTO sales (product_id, quantity) VALUES
(1, 10),
(2, 5),
(1, 15),
(3, 7),
(2, 20),
(3, 3);

SELECT product_id, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product_id
HAVING SUM(quantity) > 20;

Important Notes

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

You can use any aggregate function (SUM, COUNT, AVG, MAX, MIN) in HAVING conditions.

If you use HAVING without GROUP BY, it treats the whole result as one group.

Summary

HAVING filters grouped data based on conditions.

Use HAVING after GROUP BY to keep only groups you want.

HAVING works with aggregate functions like SUM and COUNT.