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.
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.Let's look at some examples of PostgreSQL HAVING
clause queries:
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.
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.
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.
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.
Let's go through a complete example that includes creating a table, inserting data, and using the HAVING clause to filter grouped data.
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
.
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.
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.
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.