Updated May 8, 2023
Introduction to PostgreSQL HAVING
PostgreSQL having Clause is used to set the condition where the group of rows is created by the group by Clause after group we have applying having Clause with where the condition for single rows. We don’t use clauses without the group by Clause to filter the group of rows that were not satisfying the condition. We can also use having Clause without using where and group by Clause. A clause in a select query retrieves data from a table based on a specific condition.
Syntax
Below is the syntax of the PostgreSQL having a clause.
SELECT column_name1, column_name2, Column_nameN(Column name used to fetch. The data from the table) aggregate_function (An aggregate function which used with having Clause) (column_name) table_name (Table name used to fetch data from the table) GROUP BY(Fetch data by using group by and having Clause) Column_name HAVING condition;
SELECT expression1, expression2, ... expressionN(Column name is used to fetch data), aggregate_function (expression)FROM table_name [WHERE conditions] GROUP BY expression1, expression2,expressions having condition;
Parameters
Below is the parameter description of the above syntax are as follows:
- Select: Using the “Select” function allows you to select data from a table by applying the “having” Clause.
- Column name 1 to column name N: We have to fetch the data from the specified column using having Clause
- Having Clause: We have not used having Clause without the group by Clause to filter the group of rows that was not satisfying the condition. We can also use having Clause without using where and group by Clause in.
- An aggregate function: We have used an aggregate function with having a clause in PostgreSQL. We have used an aggregate function like SUM, MIN, MAX, and AVG using having Clause.
- Table name: When retrieving data from a specific table, you must use the table name and the having Clause.
- Condition: We used a condition statement to fetch the data from the table using a where clause.
- Group by: The group by Clause is used with the having Clause in PostgreSQL. Without group by Clause, we cannot use the having Clause; we need to define the having Clause before using group by Clause in PostgreSQL.
- Expression 1 to Expression N: When retrieving data from a table, we use “Expression” to refer to a specific column name.
How does PostgreSQL HAVING clause works?
Below is the working:
- The group by Clause is used with the having Clause in PostgreSQL. Without group by Clause, we cannot use the having Clause; we need to define the having Clause before using group by Clause in PostgreSQL.
- Data from the employee table is being retrieved in the example below. In the first example, we utilised group by Clause with having Clause, and the data was successfully obtained while utilising group by Clause in having Clause.
- The second example will show an error that “ERROR: column “employee.emp_id” must appear in the GROUP BY Clause or be used in an aggregate function.”
Code:
SELECT emp_id, SUM (emp_salary) FROM employee GROUP BY emp_id HAVING SUM (emp_salary) > 20000;
SELECT emp_id, SUM (emp_salary) FROM employee HAVING SUM (emp_salary) > 20000;Output:
- The example is that we need to use a group by Clause with having Clause.
- The “HAVING” Clause in PostgreSQL establishes a condition for groups of rows created by the “GROUP BY” Clause.
- After grouping by Clause, we have applying having Clause with where the condition for single rows.
- We can also use having Clause without using where and group by Clause in PostgreSQL. A clause is used in the select query to fetch the data from the table with the specified condition.
- Having clauses is very important and useful in PostgreSQL to fetch the data using specified conditions from the table.
We have used the below aggregate function to retrieved data from the table using having Clause.
- SUM
- MIN
- MAX
- AVG
- Count
We have not used a clause without the group by Clause to filter the group of rows that were not satisfying the condition.
Example to Implement HAVING in PostgreSQL
Below is an example. Using the employee table to describe the example of having a clause in PostgreSQL is as follows.
1. Using the SUM function
In the example below, we have retrieved data from the employee table using having Clause. We have retrieving data from the employee table, which has a salary of more than 30000.
Code:
SELECT emp_id, SUM (emp_salary) FROM employee GROUP BY emp_id HAVING SUM (emp_salary) > 30000;Output:
Explanation: Example of having a clause using the SUM aggregate function in PostgreSQL.
2. Using the count function
The example below shows that the sum of employee salary with an employee id is greater than one.
Code:
SELECT emp_name, SUM (emp_salary) FROM employee GROUP BY emp_name HAVING count (emp_id) > 1;Output:
Explanation: Example of having a clause by using the aggregate count function in PostgreSQL.
3. Using the Avg function
The below example shows the sum of employee salary, which has an employee salary is greater than 10000.
Code:
SELECT emp_name, SUM (emp_salary) FROM employee GROUP BY emp_name HAVING count (emp_salary) > 10000;Output:
Explanation: Example of having a clause using the AVG function in PostgreSQL.
4. Using the MIN function
The below example shows the MIN of employee salary, which has an employee salary is greater than 10000.
Code:
SELECT emp_name, MIN (emp_salary) FROM employee GROUP BY emp_name HAVING count (emp_salary) > 10000;Output:
Explanation: Example of having a clause by using the MIN function in PostgreSQL.
5. Using the MAX function
The below example shows the MAX of employee salary, which has an employee salary is greater than 10000.
Code:
SELECT emp_name, MAX (emp_salary) FROM employee GROUP BY emp_name HAVING count (emp_id) > 1;Output:
Explanation: Example of having a clause by using the MAX function in PostgreSQL.
Advantages
Below are the advantages:
- In PostgreSQL, you can use the HAVING clause to filter results from a grouped set of data based on a specified condition. This allows you to retrieve only the groups of data that meet the specified criteria.
- The “having” clause groups data, while the “where” Clause filters single-row data.
- We need to define the group before retrieving data using having a clause in PostgreSQL.
- Having Clause allows to fetch of data from particular rows where the same rows match some condition.
- A grouping clause in PostgreSQL requires the use of a clause alongside it and cannot be used independently.
Conclusion
Using a clause to retrieve data from a specific table in PostgreSQL would be best. The GROUP BY clause forms groups of rows, and the HAVING clause enables you to set conditions for these groups of rows. This allows you to filter the groups based on aggregate values and return only the groups that meet the specified criteria.
Recommended Articles
We hope that this EDUCBA information on “PostgreSQL HAVING” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
