PostgreSQL HAVING Clause

The HAVING clause allows us to filter the grouped data after grouping.

The WHERE clause sets conditions on selected columns, while 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. Below 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

Examples

Create the COMPANY table (Download COMPANY SQL file), with the data as follows:

exampledb# 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 data grouped by the NAME field value, andnamefield count less than 2:

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

The following results are obtained:

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

Let's add a few rows 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);

At this point, the records in the COMPANY table 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 data grouped by the name field value, and with a name count greater than 1:

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

The results are as follows:

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