PostgreSQL - HAVING clause

The HAVING clause in PostgreSQL filters grouped records after the GROUP BY clause is applied. It is used to filter groups based on aggregate conditions.

• Filters grouped records based on aggregate conditions.
• Supports aggregate functions such as COUNT(), SUM(), AVG(), MIN() and MAX().

Syntax

SELECT column_1, aggregate_function(column_2)
FROM table_name
GROUP BY column_1
HAVING aggregate_function(column_2) condition;

Where:

• column_1: The column used to group the records.
• aggregate_function(): An aggregate function such as COUNT(), SUM(), AVG(), MIN() or MAX().
• table_name: The name of the table.
• GROUP BY: Groups rows with the same values.
• HAVING: Filters the grouped results based on the specified condition.

Examples

Firstly, create a sales table and insert some records into it.

CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    sales_person VARCHAR(50),
    product VARCHAR(50),
   amount DECIMAL(10,2)
);

INSERT INTO sales (sale_id, sales_person, product, amount)
VALUES
(1, 'John', 'Laptop', 800.00),
(2, 'Emma', 'Phone', 600.00),
(3, 'John', 'Monitor', 300.00),
(4, 'Michael', 'Laptop', 900.00),
(5, 'Emma', 'Tablet', 500.00),
(6, 'John', 'Keyboard', 100.00);

Output:

Example 1: HAVING with COUNT()

The following query displays sales_persons who have made more than one sale.

Query:

SELECT sales_person, COUNT(*) AS total_sales
FROM sales
GROUP BY sales_person
HAVING COUNT(*) > 1;

Output:

Example 2: HAVING with SUM()

The following query displays sales_persons whose total sales amount is greater than 1000.

Query:

SELECT sales_person, SUM(amount) AS total_amount
FROM sales
GROUP BY sales_person
HAVING SUM(amount) > 1000;

Output:

Example 3: HAVING with AVG()

The following query displays sales_persons whose average sales amount is greater than 500.

Query:

SELECT sales_person, AVG(amount) AS average_amount
FROM sales
GROUP BY sales_person
HAVING AVG(amount) > 500;

Output:

Example 4: HAVING with WHERE

The following query filters sales greater than 300 before grouping and then displays sales_persons whose total sales amount exceeds 800.

Query:

SELECT sales_person, SUM(amount) AS total_amount
FROM sales
WHERE amount > 300
GROUP BY sales_person
HAVING SUM(amount) > 800;

Output:

Example 5: HAVING with Multiple Conditions

The following query displays sales_persons who have made at least two sales and whose total sales amount exceeds 1000.

Query:

SELECT sales_person,
       COUNT(*) AS total_sales,
       SUM(amount) AS total_amount
FROM sales
GROUP BY sales_person
HAVING COUNT(*) >= 2
   AND SUM(amount) > 1000;

Output:

To know the difference between Having and Where Clause refer to this - Having vs Where Clause in SQL