PostgreSQL HAVING Clause

The HAVING clause allows us to filter data after the groups have been formed.

The WHERE clause sets conditions on the selected columns, whereas the HAVING clause sets conditions on groups created by the GROUP BY clause.

Syntax

Below is the position of the HAVING clause in a SELECT query:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

The HAVING clause must be placed after the GROUP BY clause and before the ORDER BY clause. Here is the basic syntax of the HAVING clause in a SELECT statement:

SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

Example

Create a COMPANY table (download COMPANY SQL file), with the following data:

tutorialprodb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

The following example will find groups based on the NAME field value and the count of the name field is less than 2:

SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) < 2;

The result is as follows:

name
 -------
  Teddy
  Paul
  Mark
  David
  Allen
  Kim
  James
(7 rows)

We add a few more records to the table:

INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00);
INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00);
INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00);

Now, the COMPANY table records are as follows:

id | name  | age | address      | salary
 ----+-------+-----+--------------+--------
   1 | Paul  |  32 | California   |  20000
   2 | Allen |  25 | Texas        |  15000
   3 | Teddy |  23 | Norway       |  20000
   4 | Mark  |  25 | Rich-Mond    |  65000
   5 | David |  27 | Texas        |  85000
   6 | Kim   |  22 | South-Hall   |  45000
   7 | James |  24 | Houston      |  10000
   8 | Paul  |  24 | Houston      |  20000
   9 | James |  44 | Norway       |   5000
  10 | James |  45 | Texas        |   5000
(10 rows)

The following example will find groups based on the name field value and the count of names greater than 1:

tutorialprodb-# SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) > 1;

The result is as follows:

name
-------
 Paul
 James
(2 rows)