w3resource logo


postgresql having

PostgreSQL HAVING

rating PostgreSQL HAVING has average rating 9 out of 10. Total 4 users rated.

<<PreviousNext>>

Description

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 is not used the HAVING works like a WHERE clause.

Difference between HAVING and WHERE

HAVING WHERE
HAVING clause is used for conditional retrieval of rows from a grouped result. WHERE clause with ORDER BY is used for conditional retrieval or individual rows.
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>]

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


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


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 is more than 8000 from employee table, the following SQL can be used. The default order of ORDER BY is ascending.

SQL


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 is more than 8000 according the ascending order of SUM(salary) from employee table, the following SQL can be used. The default order of ORDER BY is ascending.

SQL


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



<<PreviousNext>>