EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL HAVING
 

PostgreSQL HAVING

Priya Pedamkar
Article byPriya Pedamkar

Updated May 8, 2023

PostgreSQL HAVING

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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:

PostgreSQL HAVING - 1

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

PostgreSQL HAVING - 2

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:

PostgreSQL HAVING - 3

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:

Avg function

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:

MIN function

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:

MAX function

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.

  1. PostgreSQL ROLLUP
  2. OFFSET PostgreSQL
  3. Guide to PostgreSQL NOW()
  4. PostgreSQL RANDOM | Examples

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW