PostgreSQL – HAVING

The HAVING clause in PostgreSQL is used to filter groups of rows returned by a GROUP BY clause. It is similar to the WHERE clause, but HAVING is used to filter aggregated data.

In this tutorial, we will learn how to use the HAVING clause with step-by-step examples, starting with creating a database and table, inserting data, and then applying HAVING in different scenarios.

Step 1: Create a Database

First, create a database named company to store our data.

</>
Copy
CREATE DATABASE company;

Connect to the database:

</>
Copy
\c company;
PostgreSQL - HAVING Clause Example Setup

Step 2: Create a Table

Create a table named sales to store sales data.

</>
Copy
CREATE TABLE sales (
    sale_id SERIAL PRIMARY KEY,
    region VARCHAR(50),
    product VARCHAR(50),
    quantity INT,
    revenue DECIMAL(10, 2)
);
PostgreSQL - HAVING Clause Example Setup

Step 3: Insert Data

Insert sample sales data into the sales table.

</>
Copy
INSERT INTO sales (region, product, quantity, revenue)
VALUES 
('North', 'Laptop', 5, 5000),
('South', 'Laptop', 3, 3000),
('North', 'Tablet', 10, 2500),
('South', 'Tablet', 8, 2000),
('East', 'Laptop', 2, 2000),
('East', 'Tablet', 6, 1500);
PostgreSQL - HAVING Clause Example Setup

Step 4: Using HAVING to Filter Groups

Group the data by region and calculate the total revenue for each region. Then, filter regions where total revenue is greater than $3000:

</>
Copy
SELECT region, SUM(revenue) AS total_revenue
FROM sales
GROUP BY region
HAVING SUM(revenue) > 3000;

This query filters out groups (regions) where the total revenue is $3000 or less.

PostgreSQL - HAVING Clause Example - Using HAVING to Filter Groups

Step 5: Combining HAVING with Aggregate Functions

Filter products sold in each region where the total quantity is greater than 10:

</>
Copy
SELECT region, product, SUM(quantity) AS total_quantity
FROM sales
GROUP BY region, product
HAVING SUM(quantity) > 10;

This query shows regions and products where more than 10 units have been sold.

PostgreSQL - HAVING Clause Example - Combining HAVING with Aggregate Functions

Step 6: HAVING with Multiple Conditions

Filter regions where the total revenue exceeds $3000 and the total quantity is greater than 10:

</>
Copy
SELECT region, SUM(quantity) AS total_quantity, SUM(revenue) AS total_revenue
FROM sales
GROUP BY region
HAVING SUM(revenue) > 3000 AND SUM(quantity) > 10;

This query uses multiple conditions in the HAVING clause to filter the groups.

PostgreSQL - HAVING Clause Example - HAVING with Multiple Conditions

Step 7: Combining HAVING and ORDER BY

Filter regions where total revenue exceeds $3000 and sort the results by total revenue in descending order:

</>
Copy
SELECT region, SUM(revenue) AS total_revenue
FROM sales
GROUP BY region
HAVING SUM(revenue) > 3000
ORDER BY total_revenue DESC;

This query filters and sorts the grouped data.

PostgreSQL - HAVING Clause Example - Combining HAVING and ORDER BY

Conclusion

The HAVING clause is an essential tool for filtering aggregated data in PostgreSQL. It allows you to apply conditions on grouped rows, enabling more precise data analysis. Practice these examples to master the HAVING clause.