PostgreSQL HAVING Clause

PostgreSQL HAVING Clause

The PostgreSQL HAVING clause is used to filter groups of rows created by the GROUP BY clause based on a specified condition. This clause is essential for performing aggregate filtering on grouped data.

---

Syntax

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

The HAVING clause has the following components:

• column1, column2, ...: The columns to retrieve from the table.
• aggregate_function(column2): The aggregate function to apply to each group.
• table_name: The name of the table from which to retrieve data.
• condition: The condition to filter groups.

---

Example PostgreSQL HAVING Clause Queries

Let's look at some examples of PostgreSQL HAVING clause queries:

1. Basic HAVING Clause Example

SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 10;

This query retrieves the department_id and the count of employees in each department from the employees table. It groups the results by department_id and filters to include only departments with more than 10 employees.

2. HAVING with Multiple Conditions

SELECT department_id, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 50000 AND COUNT(*) > 5;

This query retrieves the department_id and the average salary of employees in each department from the employees table. It groups the results by department_id and filters to include only departments with an average salary greater than 50000 and more than 5 employees.

3. HAVING with Aggregate Function

SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id
HAVING SUM(salary) > 200000;

This query retrieves the department_id and the total salary of employees in each department from the employees table. It groups the results by department_id and filters to include only departments with a total salary greater than 200000.

4. HAVING with ORDER BY Clause

SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 10
ORDER BY employee_count DESC;

This query retrieves the department_id and the count of employees in each department from the employees table. It groups the results by department_id, filters to include only departments with more than 10 employees, and sorts the results by employee_count in descending order.

---

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the HAVING clause to filter grouped data.

Step 1: Creating a Table

This step involves creating a new table named employees to store employee data.

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    salary NUMERIC(10, 2),
    department_id INT
);

In this example, we create a table named employees with columns for id, first_name, last_name, email, salary, and department_id.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the employees table.

INSERT INTO employees (first_name, last_name, email, salary, department_id)
VALUES ('John', 'Doe', 'john.doe@example.com', 50000, 1);

INSERT INTO employees (first_name, last_name, email, salary, department_id)
VALUES ('Jane', 'Smith', 'jane.smith@example.com', 60000, 2);

INSERT INTO employees (first_name, last_name, email, salary, department_id)
VALUES ('Jim', 'Brown', 'jim.brown@example.com', 55000, 1);

INSERT INTO employees (first_name, last_name, email, salary, department_id)
VALUES ('Emily', 'Jones', 'emily.jones@example.com', 70000, 2);

Here, we insert data into the employees table.

Step 3: Filtering Grouped Data with HAVING

This step involves using the HAVING clause to filter grouped data in the employees table.

-- Basic HAVING Clause

SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 1;

-- HAVING with Multiple Conditions

SELECT department_id, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 50000 AND COUNT(*) > 1;

-- HAVING with Aggregate Function

SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id
HAVING SUM(salary) > 100000;

-- HAVING with ORDER BY Clause

SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 1
ORDER BY employee_count DESC;

These queries demonstrate how to use the HAVING clause to filter grouped data in the employees table, including using basic conditions, multiple conditions, aggregate functions, and sorting grouped results with the ORDER BY clause.

Conclusion

The PostgreSQL HAVING clause is a fundamental tool for filtering groups of rows created by the GROUP BY clause based on specified conditions. Understanding how to use the HAVING clause and its syntax is essential for effective data analysis and reporting in PostgreSQL databases.