PostgreSQL HAVING Clause

The HAVING clause allows us to filter the data in each group after grouping.

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

Syntax

The position of the HAVING clause in the SELECT query is as follows:

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 in the SELECT statement. The basic syntax of the HAVING clause in the SELECT statement is as follows:

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

Online example

Create COMPANY table (Download COMPANY SQL file ），data content is as follows：

w3codeboxdb# 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 the records grouped by the NAME field value and name (name) The count of the field is less than 2 Data:

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)

We will add several data 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);

At this time, the records of 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 the records grouped by the 'name' field value and the count of names is greater than 1 Data:

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

The results obtained are as follows:

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