PostgreSQL - HAVING Clause

PostgreSQL - The HAVING Clause

The PostgreSQL HAVING clause is used to filter the results of a GROUP BY clause. While the WHERE clause filters individual rows before they are grouped, the HAVING clause filters entire groups after the aggregation has been performed.

WHERE vs. HAVING: The Key Difference

• WHERE Clause: Filters rows based on conditions applied to individual columns. It is processed before GROUP BY and aggregate functions (like COUNT(), SUM(), AVG()).
• HAVING Clause: Filters groups based on conditions applied to the results of aggregate functions. It is processed after the GROUP BY clause.

A simple rule of thumb: If your condition involves an aggregate function, it belongs in the HAVING clause. Otherwise, it belongs in the WHERE clause for better performance.

Syntax

The HAVING clause must appear after the GROUP BY clause and before the ORDER BY clause.

SELECT
    column1,
    aggregate_function(column2)
FROM
    table_name
WHERE
    [row_conditions]
GROUP BY
    column1
HAVING
    [group_conditions]
ORDER BY
    [column | expression];

Example 1: Finding Departments with High Average Salaries

Let’s use an employees table to find all departments where the average salary is greater than $75,000.

-- Sample Data in employees table:
-- department    | salary
-- --------------+--------
-- Engineering   | 90000
-- Engineering   | 80000
-- Sales         | 70000
-- Sales         | 85000
-- HR            | 60000

SELECT
    department,
    AVG(salary) AS average_salary
FROM
    employees
GROUP BY
    department
HAVING
    AVG(salary) > 75000;

This query first groups employees by department, calculates the average salary for each group, and then the HAVING clause filters these groups, returning only those with an average salary above $75,000.

Expected Result:

 department  | average_salary
-------------+----------------
 Engineering | 85000.00
 Sales       | 77500.00
(2 rows)

Example 2: Combining WHERE and HAVING

Let’s find the number of products in each category, but only for products with a price over $20, and only show categories that have more than 5 such products.

SELECT
    category_id,
    COUNT(product_id) AS product_count
FROM
    products
WHERE
    price > 20.00 -- Filter individual rows first
GROUP BY
    category_id
HAVING
    COUNT(product_id) > 5; -- Filter groups after counting

In this example:

• The WHERE clause first filters out all products costing $20 or less.
• The GROUP BY clause then groups the remaining products by their category.
• Finally, the HAVING clause filters these groups, keeping only those with a count greater than 5.