HAVING Clause in SQL

This guide covers having clause in SQL with practical context and easy-to-follow details. Use it to understand the subject and apply the information confidently.

• WHERE Is the conditional statement that returns the result compared to each line.
• HAVING Is a conditional statement that returns the collated result for the group created by the GROUP BY clause.

So after GROUP BY, only HAVING can be used and WHERE cannot be used after GROUP BY.

Syntax to Use HAVING in SQL

The location of the HAVING clause in an SQL query is as follows:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

Note : The HAVING clause must follow the GROUP BY clause in a query and precede the ORDER BY clause if used.

Here is the syntax of the SELECT statement including the HAVING clause:

SELECT cot1, cot2
FROM bang1, bang2
WHERE [ dieu_kien ]
GROUP BY cot1, cot2
HAVING [ dieu_kien ]
ORDER BY cot1, cot2

Examples of HAVING in SQL

Suppose the NHANVIEN Table has the following records:

 +----+----------+-----+-----------+----------+ | ID | TEN |TUOI | DIACHI | LUONG | +----+----------+-----+-----------+----------+ | 1 | Thanh | 32 | Haiphong | 2000.00 | | 2 | Loan | 25 | Hanoi | 1500.00 | | 3 | Nga | 23 | Hanam | 2000.00 | | 4 | Manh | 25 | Hue | 6500.00 | | 5 | Huy | 27 | Hatinh | 8500.00 | | 6 | Cao | 22 | HCM | 4500.00 | | 7 | Lam | 24 | Hanoi | 10000.00 | +----+----------+-----+-----------+----------+

To display a record with an age that is greater than or equal to 2 times, run the following command:

SQL > SELECT ID, TEN, TUOI, DIACHI, LUONG
FROM NHANVIEN
GROUP BY tuoi
HAVING COUNT(tuoi) >= 2;

The result is:

 +----+----------+-----+-----------+----------+ | ID | TEN |TUOI | DIACHI | LUONG | +----+----------+-----+-----------+----------+ | 2 | Loan | 25 | Hanoi | 1500.00 | +----+----------+-----+-----------+----------+

In the next section, we will take a look at Transaction in SQL , remember to follow it.

Previous article: Frame VIEW in SQL

Next post: TRANSACTION in SQL