PostgreSQL HAVING statement

PostgreSQL HAVING statement is used in combination with GROUP BY statement to limit groups of returned strings only to those whose condition is TRUE.

The syntax for the HAVING statement in PostgreSQL

SELECT expression1_id, expression2_id,. expression_n_id,
aggregate_function
FROM tabs
[WHERE conds]
GROUP BY expression1_id, expression2_id,... expression_n_id
HAVING cond;

Statement parameters and arguments

• aggregate_function – This can be an aggregate function such as sum, count, min, max, or avg.
• expression1_id, expression_2id,… expression_n_id – Expressions which are not contained in the aggregate function and must be included in the GROUP BY operator.
• WHERE conds – Optional. These are conditions for selecting records.
• HAVING cond – This is another condition applied only to aggregate results to limit groups of returned lines. Only those groups whose condition is evaluated as TRUE will be included in the result set.

Example using the sum function

Let’s consider a HAVING example that uses the sum function.
You can also use the sum function to return a department and the sum(salary) function to that department. The PostgreSQL statement HAVING will filter the results so that only departments with a total salary greater than 25000 USD will be returned.

SELECT depart, sum(salary) AS "Salaries for the department"
FROM empls
GROUP BY depart
HAVING sum(salary) > 25000;

Example using count function

Let’s see how to use the HAVING operator with the count function.
You can use the count function to return a department and the number of employees (for that department) that have “Active” status. The PostgreSQL statement HAVING will filter the results so that only departments with a maximum of 35 employees will be returned.

SELECT department, count(*) AS "Number of employees"
FROM empls
WHERE status = 'Active'
GROUP BY depart
HAVING count(*) <= 35;

Example using min function

Let’s now see how to use the HAVING operator with the min function.
You can also use the min function to return the name of each department and the minimum wage in that department. PostgreSQL operator HAVING returns only those departments where the minimum wage is less than 36000 USD.

SELECT depart, min(salary) AS "Lowest salary".
FROM empls
GROUP BY depart
HAVING min(salary) < 36000;

Example using max function

Finally, let’s consider how to use the HAVING operator with the max function.
For example, you can also use the max function to return the name of each department and the maximum salary of the department. PostgreSQL statement HAVING will return only those departments whose maximum salary is more or equal to 31950 USD.

SELECT depart, max(salary) AS "Highest salary"
FROM empls
GROUP BY depart
HAVING max(salary) >= 31950;

PostgreSQL: Group By Having | Course