The HAVING statement permits us to choose specific columns where the capacity's outcome meets some condition.
The WHERE proviso places conditions on the chose segments, while the HAVING statement places conditions on gatherings made by the GROUP BY condition.
Syntax
Coming up next is the situation of the HAVING proviso in a SELECT inquiry −
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
The HAVING statement should follow the GROUP BY provision in a question and should likewise go before the ORDER BY condition whenever utilized. Coming up next is the language structure of the SELECT assertion, including the HAVING proviso −
SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2
Example
Consider the table COMPANY having records as follows −
# 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)
Coming up next is a model, which would show record for which the name tally is under 2 −
testdb-# SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) < 2;
This would deliver the accompanying outcome −
name
-------
Teddy
Paul
Mark
David
Allen
Kim
James
(7 rows)
Presently, let us make three additional records in COMPANY table utilizing the accompanying INSERT proclamations −
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);
Presently, our table has the accompanying records with copy names −
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)
Coming up next is the model, which would show record for which the name tally is more noteworthy than 1 −
testdb-# SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) > 1;
This would deliver the accompanying outcome −
name
-------
Paul
James
(2 rows)