PostgreSQL HAVING

Last update on November 13 2023 09:57:18 (UTC/GMT +8 hours)

HAVING Clause

The HAVING clause is used to specify which individual group(s) are to be displayed, that is , the desire groups that you return on the basis of aggregate functions.

The where clause cannot be used to get return the desire groups. The WHERE clause can only use to restrict individual rows.

When the GROUP BY has not used the HAVING works like a WHERE clause.

Difference between HAVING and WHERE

WHERE clause with ORDER BY is used for conditional retrieval or individual rows.

HAVING	WHERE
HAVING clause is used for conditional retrieval of rows from a grouped result.
Applies only groups as a whole.	Applies only individual rows.
HAVING cannot use without grouping.	WHERE can use without grouping.

Syntax:

SELECT [DISTINCT] <column_list>| <expression>
FROM <table>[,<table>][WHERE <condition>]
GROUP BY <column | expression>
[HAVING <condition>]
<condition>

Parameters

Name	Description
column_list	Name of the columns
table	Name of the table(s)
DISTINCT	This clause is optional. It indicates uniqueness.
expression	It may be arguments or statements etc
condition	It is the criteria of a query.

PostgreSQL HAVING example1

The sample table

postgresql sample table employee example1

If we want to display the list of average salary for all departments having more than 3 employees from employee table, the following SQL can be used.

SQL

Code:

SELECT deptno, AVG(salary)
FROM employee
GROUP BY deptno
HAVING COUNT(*)>3;

Output:

PostgreSQL GROUP BY with MAX

If we want to get the list of all employees whose maximum salary is more than 8000 from employee table, the following SQL can be used.

SQL

Code:

SELECT designame, MAX(salary)
FROM employee
GROUP BY designame
HAVING MAX(salary)>8000;

Output:

PostgreSQL HAVING and WHERE

If we want to list the total salary, maximum and minimum salary and the average salary of employees designation wise which belongs to the department no 15 and salary are more than 8000 from employee table, the following SQL can be used. The default order of ORDER BY is ascending.

SQL

Code:

SELECT designame, SUM(salary),MAX(salary),MIN(salary),AVG(salary)
FROM employee
WHERE deptno=15
GROUP BY designame
HAVING AVG(salary)>7000;

Output:

Explanation

In the above example, the WHERE clause will retrieve the rows of department 15 from employee table. The GROUP BY clause will group the fetched result designation wise and apply the aggregate functions. After the group has been made, the HAVING clause will display the rows satisfying the specified condition.

PostgreSQL HAVING and ORDER BY

If we want to list the total salary, maximum and minimum salary and the average salary of employees designation wise which belongs to the department no 15 and salary are more than 8000 according to the ascending order of SUM(salary) from employee table, the following SQL can be used. The default order of ORDER BY is ascending.

SQL

Code:

SELECT designame, SUM(salary),MAX(salary),MIN(salary),AVG(salary)
FROM employee
WHERE deptno=15
GROUP BY designame
HAVING AVG(salary)>7000
ORDER BY SUM(salary);

Output:

postgresql having order by in ascending order

Previous: ORDER BY
Next: AGGREGATE FUNCTIONS