EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL HAVING

PostgreSQL HAVING

By Priya PedamkarPriya Pedamkar

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.

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: 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
PROGRAMMING LANGUAGES Course
502+ Hours of HD Videos
54 Courses
4 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SELENIUM Certification Course
57+ Hours of HD Videos
15 Courses
9 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
IOT System - Design & Develop an IOT System
65+ Hours of HD Videos
7 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
JENKINS Certification Course
19+ Hours of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
POSTGRESQL Certification Course
 17+ Hour of HD Videos
4 Courses
1 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - 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

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
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*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?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more