Tutorialsteacher

Follow Us

Articles
  • C#
  • C# OOP
  • ASP.NET Core
  • ASP.NET MVC
  • LINQ
  • Inversion of Control (IoC)
  • Web API
  • JavaScript
  • TypeScript
  • jQuery
  • Angular 11
  • Node.js
  • D3.js
  • Sass
  • Python
  • Go lang
  • HTTPS (SSL)
  • Regex
  • SQL
  • SQL Server
  • PostgreSQL
  • MongoDB
  • PostgreSQL - Get Started
  • Install PostgreSQL
  • Connect to PostgreSQL DB
  • Create Database
  • Create Table
  • Copy Table
  • Drop Table
  • Drop Database
  • Truncate Table
  • ALTER Table
  • Rename Table
  • Rename Columns
  • Add Columns
  • Modify Column Type
  • Set Default Value of Column
  • Remove Columns
  • Add Constraints to Table
  • Insert Data
  • Upsert Data
  • Update Data
  • Delete Data
  • SELECT Statement
  • WHERE Clause
  • GROUP BY Clause
  • HAVING Clause
  • ORDER BY Clause
  • DISTINCT Clause
  • Inner Join
  • Left Outer Join
  • Right Outer Join
  • Full Outer Join
  • Self Join
  • Natural Join
  • Cross Join
  • LIMIT OFFSET Clause
  • GROUPING SETS
  • GROUPING() Function
  • GROUP BY CUBE
  • GROUP BY ROLLUP
  • Sub Query
  • ALL Operator
  • ANY Operator
  • UNION Operator
  • INTERSECT Operator
  • EXCEPT Operator
  • IS NULL Operator
  • BETWEEN Operator
  • LIKE Operator
  • CAST Operator
  • CASE Expressions
  • NULLIF()
  • COALESCE()
  • GREATEST(), LEAST()
  • WITH Queries (CTE)
  • Constraints
  • NOT NULL Constraint
  • Unique Constraint
  • Check Constraint
  • Primary Key
  • Foreign Key
  • Sequence
  • Serial Type
  • Identity Columns
  • Generated Columns
  • Data Types
  • Boolean Type
  • Character Type
  • Integer Type
  • Numeric Type
  • Date Type
  • Time Type
  • TimeStamp Type
  • Interval Type
  • Array Type
  • Json Type
Entity Framework Extensions - Boost EF Core 9
  Bulk Insert
  Bulk Delete
  Bulk Update
  Bulk Merge

PostgreSQL HAVING Clause

In PostgreSQL, the HAVING clause is used after the GROUP BY clause to filter the result of the GROUP BY clause based on the specified condition. The HAVING condition is the same as the WHERE condition that returns TRUE or FALSE.

The columns used in the HAVING condition must be used in the GROUP BY clause unless an aggregate function is used with the column. Currently, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, and FOR KEY SHARE cannot be specified with HAVING.

Syntax:
SELECT <column_list>
FROM <table_name>
[ WHERE <conditions>]
[ GROUP BY <column1>, <column2>...]
[ HAVING condition ]
[ ORDER BY ]

PostgreSQL evaluates the HAVING clause after FROM, WHERE, GROUP BY but before SELECT, ORDER BY, and LIMIT clauses. The HAVING clause is evaluated before the SELECT clause, so the column alias cannot be used in the HAVING clause.

Let's see how to use the GROUP BY HAVING clause using the following employee table.

The following query gets the total salary for each department using the GROUP BY clause.

Example: GROUP BY
SELECT dept_id, SUM(salary) AS "Total Salary" FROM employee
GROUP BY dept_id;

The above query displays the following result in pgAdmin:

Now, use the HAVING clause to filter the grouped result further. For example, the following returns the rows where the total salary is more than 200000 using the HAVING clause.

Example: HAVING Clause
SELECT dept_id, SUM(salary) AS "Total Salary" FROM employee
GROUP BY dept_id
HAVING SUM(salary) &gt; 200000;

Note that the group is formed on the dept_id column and the SELECT clause uses the aggregate function SUM() to get the total salary for each dept_id. The HAVING clause also uses the SUM() function in the condition to filter the grouped data because the salary column is not used with the GROUP BY clause. If you don't use any aggregate function then it will display the following error.

HAVING clause with Multiple Columns

You can specify multiple conditions in the HAVING clause. Here, for each department, we will find the number of employees and maximum salary been paid to employees using the GROUP BY clause. The HAVING clause will be applied on grouped rows to fetch only departments that have more than 3 employees and a maximum salary of more than 75000.

Example: Multi-columns HAVING Clause
SELECT dept_id, COUNT(emp_id) AS "No of Employees", MAX(salary) as "Max Salary"
FROM employee
GROUP BY dept_id
HAVING COUNT(emp_id) > 3 AND MAX(salary) &gt; 75000;

HAVING clause with WHERE clause

The HAVING clause can be used along with the WHERE clause also. The WHERE clause conditions are applied first on individual rows to filter out data, after that GROUP BY does the grouping of data and the HAVING clause to filter grouped data.

Let's find out which department's Male employees' total salary is more than 100000. First filter out only Male employees using the WHERE clause and then for each department get SUM of Salary and finally select department using HAVING clause.

Example: WHERE & HAVING
SELECT dept_id, SUM(salary) FROM Employee
WHERE gender = 'M'
GROUP By dept_id
HAVING SUM(salary) &gt; 100000;
TUTORIALSTEACHER.COM

TutorialsTeacher.com is your authoritative source for comprehensive technologies tutorials, tailored to guide you through mastering various web and other technologies through a step-by-step approach.

Our content helps you to learn technologies easily and quickly for learners of all levels. By accessing this platform, you acknowledge that you have reviewed and consented to abide by our Terms of Use and Privacy Policy, designed to safeguard your experience and privacy rights.

[email protected]

ABOUT USTERMS OF USEPRIVACY POLICY
copywrite-symbol

2024 TutorialsTeacher.com. (v 1.2) All Rights Reserved.