w3resource
MySQL Tutorial

PostgreSQL HAVING

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 having example1

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 with max

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:

postgresql having with where

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

Many of our valued users post comments along with piece of code. Disqus may, false positively, consider those piece of code as Bad or Starnge syntax and send those comments to spam automatically. Even if that happens, we will make sure those useful comments are taken out of spam and approved. It may take a couple of days for that though, but any useful comment will be brought to public view for sure. We regret if that happend to any user. You may write us directly regarding this to - w3resource[at]yahoo[dot]com.