Home » PostgreSQL Having Clause

PostgreSQL Having Clause

by Online Tutorials Library

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:

PostgreSQL Having clause

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.

PostgreSQL Having clause

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.

PostgreSQL Having clause

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:

PostgreSQL Having clause

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:

PostgreSQL Having clause

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:

PostgreSQL Having clause


You may also like