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:

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:

Output

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

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:

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:

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:

Output

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