EDUCBA

Home » Data Science » Data Science Tutorials » PostgreSQL Tutorial » PostgreSQL HAVING

PostgreSQL HAVING

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

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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:

PostgreSQL HAVING - 1

  • 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:

PostgreSQL HAVING - 2

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:

PostgreSQL HAVING - 3

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:

Avg function

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:

MIN function

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:

MAX function

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 –

  1. PostgreSQL ROLLUP
  2. PostgreSQL IN Operator
  3. PostgreSQL OFFSET
  4. Guide to PostgreSQL NOW()
  5. PostgreSQL RANDOM | Examples
0 Shares
Share
Tweet
Share