PostgreSQL HAVING

Summary: in this tutorial, you’ll learn how to use the PostgreSQL HAVING clause to filter groups returned by the GROUP BY clause.

Getting started with the PostgreSQL HAVING clause #

The HAVING clause is a powerful tool, allowing you to specify a condition to filter groups that the GROUP BY clause returns.

Here’s the syntax of the HAVING clause:

SELECT column1, column2
FROM table_name
GROUP BY column1, column2
HAVING condition;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• First, specify the table name where you want to retrieve the rows in the FROM clause.
• Second, define one or more columns for grouping in the GROUP BY clause.
• Third, provide a condition in the HAVING clause to filter groups returned by the GROUP BY clause. If the condition is true, PostgreSQL includes the group in the result set.
• Finally, specify columns (or expressions) you want to include in the final result set in the SELECT clause.

PostgreSQL evaluates the clauses in the following order:

• FROM
• GROUP BY
• HAVING
• SELECT

Since PostgreSQL evaluates the HAVING clause before the SELECT clause, column aliases cannot be accessible in the HAVING clause.

Unlike the WHERE clause that filters rows based on individual rows, the HAVING clause filters groups based on the results of the GROUP BY clause.

This means that PostgreSQL applies the HAVING clause after the GROUP BY clause has grouped rows, allowing you to filter the groups based on condition.

Setting up sample tables #

We’ll use the following inventories table for the demonstration purposes:

CREATE TABLE warehouses(
   warehouse_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   warehouse_name VARCHAR(255) NOT NULL UNIQUE 
);

CREATE TABLE brands(
   brand_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   brand_name VARCHAR(255) NOT NULL UNIQUE   
);


CREATE TABLE inventories(
   id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   product_name VARCHAR(255) NOT NULL,
   quantity INT NOT NULL,
   warehouse_id int not null,
   brand_id int not null,
   FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id) ON DELETE CASCADE,
   FOREIGN KEY (brand_id) REFERENCES brands(brand_id) ON DELETE CASCADE
);



INSERT INTO warehouses (warehouse_name) 
VALUES ('San Jose'),
       ('San Francisco');


INSERT INTO brands (brand_name) 
VALUES ('Apple'),
('Samsung'),
('Google');



INSERT INTO inventories (product_name, quantity, warehouse_id, brand_id) 
VALUES 
('iPhone 15', 55, 1, 1),
('iPhone 14 Pro Max', 15,  2, 1),
('iPhone 13', 35, 2, 1),
('Galaxy S24', 50,  1, 2),
('Galaxy Note 23', 40,  2, 2),
('Galaxy Z Fold 6', 25, 2, 2),
('Galaxy Z Flip 6', 15, 2, 2),
('Pixel 9', 50,  1, 3),
('Pixel 8 Pro', 25, 2, 3),
('Pixel Fold', 60, 1, 3);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PostgreSQL HAVING clause example #

The following statement uses the GROUP BY clause to provide the product quantity per warehouse:

SELECT
  warehouse_id,
  SUM(quantity)
FROM
  inventories
GROUP BY
  warehouse_id;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

It returns two rows that include warehouse id and quantity.

The following statement uses the HAVING clause to retrieve only warehouses with a product quantity greater than 200:

SELECT
  warehouse_id,
  SUM(quantity) qty
FROM
  inventories
GROUP BY
  warehouse_id
HAVING
  SUM(quantity) > 200;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 warehouse_id | qty
--------------+-----
            1 | 215Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PostgreSQL evaluates the HAVING clause after the SELECT clause; you cannot access the qty column alias in the HAVING clause.

How it works.

First, the GROUP BY clause summary rows from the inventories table into two groups:

Second, the HAVING clause filters the groups based on total quantity and includes only the group that makes the condition true:

Third, the SELECT statement retrieves the warehouse_id and sum columns, granting the column alias:

PostgreSQL HAVING clause with complex conditions #

The following example uses the HAVING clause to return the total product quantity for each combination of warehouse and brand and include only brands 1 and 2 and only if the quantity for those brands exceeds 50:

SELECT
  warehouse_id,
  brand_id,
  SUM(quantity)
FROM
  inventories
GROUP BY
  warehouse_id,
  brand_id
HAVING
  brand_id IN (1, 2)
  AND SUM(quantity) > 50;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 warehouse_id | brand_id | sum
--------------+----------+-----
            1 |        1 |  55
            2 |        2 |  80

How it works.

First, the GROUP BY returns the total product quantity by warehouse and brand:

 warehouse_id | brand_id | sum
--------------+----------+-----
            1 |        1 |  55
            2 |        3 |  25
            1 |        3 | 110
            2 |        2 |  80
            1 |        2 |  50
            2 |        1 |  50

Second, the HAVING clause filters the groups by including only brand_id 1 and 2 and product quantity more significant than 50:

 warehouse_id | brand_id | sum
--------------+----------+-----
            1 |        1 |  55
            2 |        2 |  80

Summary #

• Use the PostgreSQL HAVING clause to filter groups that the GROUP BY clause returns.

Quiz #