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:
| Parameters | Description |
|---|---|
| GROUP BY clause | It is used to return rows grouped by column1. |
| Having clause | It is used to define a condition which filter the sets. |
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:
| Having clause | Where clause |
|---|---|
| The HAVING clause allows us to filter groups of rows as per the defined condition. | The WHERE clause permits us to filter rows according to a defined condition. |
| The HAVING clause is useful to groups of rows. | The WHERE clause is applied to rows only. |
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:
