Introduction to PostgreSQL HAVING
PostgreSQL having clause is used to set the condition where the group of rows 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 in PostgreSQL. Having a clause is used in the select a query to fetch the data from the table with the specified 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: Select is used to select data from the table using 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 which was not satisfying the condition. We can also use having clause without using where and group by clause in PostgreSQL.
- 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, AVG using having clause.
- Table name: Table name is used to fetch the data from a specified table name using having clause
- Condition: We have used a condition statement to fetch the data from the table by 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 having clause before using group by clause in PostgreSQL.
- Expression 1 to Expression N: Expression is nothing but a column name used to fetch data from the table.
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 having clause before using group by clause in PostgreSQL.
- In the below example, we have retrieving data from the employee table. In the first example, we have used group by clause with having clause while using group by clause in having clause data was retrieved without error.
- In the second example, it 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 that we need to use a group by clause with having clause.
- PostgreSQL having clause is used to set the condition where the group 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. Having 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 below example, we have retrieving data from the employee table by 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 by using the SUM aggregate function in PostgreSQL.
2. Using count function
The below example shows the sum of employee salary which have 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 count aggregate function in PostgreSQL.
3. Using 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 by using the AVG function in PostgreSQL.
4. Using MIN function
The below example shows 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 MIN function in PostgreSQL.
5. Using MAX function
The below example shows 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 MAX function in PostgreSQL.
Advantages of using HAVING in PostgreSQL
Below are the advantages:
- Having a clause is used for the conditional retrieval of results from a group of the result.
- Having clause is used to group of data while where clause is used single row data.
- We need to define the group before retrieving data using having a clause in PostgreSQL.
- Having clause is allows to fetch data from particular rows where the same rows match some condition.
- Having a clause is used with a grouping clause; it cannot use without a grouping clause in PostgreSQL.
Conclusion
Having a clause is very important to fetch data from the specified table in PostgreSQL. PostgreSQL having clause is used to set the condition where the group of rows created by the group by clause after group we have applying having clause with where condition for single rows.
Recommended Articles
This is a guide to PostgreSQL HAVING. Here we discuss syntax, working on having a clause in PostgreSQL with examples to implement and advantages. You can also go through our other related articles to learn more –
