PostgreSQL Having clause

In this section, we are going to understand the working of the HAVING clause in PostgreSQL.

The having clause is used to specify a search condition for a group or an aggregate. And it is regularly used with the GROUP BY clause to filter groups or aggregates based on a detailed condition.

Syntax of PostgreSQL having clause

The basic syntax of PostgreSQL HAVING clause is as follows:

SELECT column1, aggregate_function (column2)
FROM table1, table2  
WHERE [ conditions ]  
GROUP BY column1, column2  
HAVING [ conditions ]  
ORDER BY column1, column2  

In the above syntax, we used the following parameters:

Note: In PostgreSQL, we can add other clauses of the SELECT command such as LIMIT, JOIN, and FETCH. Subsequently, the HAVING clause is working before the SELECT clause.

In PostgreSQL, the HAVING clause works in below format:

We cannot use the column aliases in the HAVING clause because, when assessing the HAVING clause, the column aliases defined in the SELECT clause are not accessible.

Difference between having and where clauses

Let us see the difference between HAVING Clause and WHERE Clause:

Examples of PostgreSQL HAVING clause

Let us see some examples of having clause in PostgreSQL. So, here we will take the employee table, which we created in the earlier topics of PostgreSQL tutorial.

Example of SUM() function using PostgreSQL HAVING clause

In the below example, we are using the GROUP BY clause with the SUM() function to identify every employee's total salary:

SELECT emp_id, SUM (salary)
FROM employee
GROUP BY emp_id;

Output

In the below example, we are using the GROUP BY clause with the SUM() function to identify every employee's total salary:

SELECT emp_id, SUM (salary)
FROM employee
GROUP BY emp_id;

Output

After executing the above command, we will get the below output, which displays the sum of each employee's salary based on their emp_id.

After that, we will add the HAVING clause in the above command for selectingthose employees whose salary is more than 25000:

SELECT emp_id, first_name, SUM (salary)
FROM employee
GROUP BY first_name, emp_id
HAVING SUM (salary) > 25000
order by first_name DESC;

Output

After executing the above command, we will get the below result, which displays those employees whose salary is more than 25000:

Example of COUNT() function using PostgreSQL HAVING clause

The below command is used to identify the number of employees with the help of the GROUP BY clause:

SELECT first_name, COUNT (emp_id)
FROM employee
GROUP BY first_name;

Output

Once we implemented the above command, we will get the below output; which displays employees' count:

After that, we will add the HAVING clause in the above command to select the first_name with less than two employees:

SELECT first_name, COUNT (emp_id)
FROM employee
GROUP BY first_name
HAVING COUNT (emp_id)< 2;

Output

After executing the above command, we will get the below result: