SQL Having Clause

By | September 13, 2020
SQL Having Clause

Like the WHERE clause HAVING clause is used to put a condition on a statement, in SQL we can not use the WHERE statement with aggregated statements such as GROUP BY, so there we use the HAVING clause to set a condition.

SQL Having Clause

In sort HAVING is a replace of WHERE clause for the GROUP BY statements.

Vamware

Having Syntax

SELECT column_name_1, column_name_2...
FROM table_name
WHERE condition
GROUP BY column_name_n
HAVING condition
ORDER BY column_name_n;

Example

For the query consider these two sample tables:

Students

+------+--------+------+--------+-------+----------+
| id   | name   | age  | grades | marks | Trade    |
+------+--------+------+--------+-------+----------+
|    1 | Luffy  |   16 | A      |   970 | Science  |
|    2 | Naruto |   18 | A      |   960 | Humanity |
|    3 | Zoro   |   20 | A      |   940 | Commerce |
|    4 | Sanji  |   21 | B      |   899 | Humanity |
|    5 | Nami   |   17 | B      |   896 | Science  |
|    6 | Robin  |   20 | B      |   860 | Humanity |
|    7 | Goku   |   27 | B      |   860 | Humanity |
+------+--------+------+--------+-------+----------+

Library

+---------+-------------------+------------+
| Book_ID | Book_Name         | Student_ID |
+---------+-------------------+------------+
|    1124 | One Hundred years |          1 |
|    1104 | The Great Escape  |          2 |
|    1209 | Beloved           |          6 |
|    1111 | Hollow            |          4 |
|    2351 | Invisible Man     |          3 |
|    1034 | A Passage         |          2 |
|    1211 | Hero              |          6 |
|    1188 | Your Name         |          5 |
|    1211 | Hero              |          6 |
|    1000 | My Hope           |          8 |
|    1000 | Go Away           |         10 |
+---------+-------------------+------------+

Query: Show the name, id, age and number of books, of those students who have borrowed more than 1 books from the libraries.

SELECT id, name, age, COUNT(Book_Name) AS BOOKS
FROM Students Join Library
ON id = Student_ID
GROUP BY name
HAVING BOOKS>1;

Output

+------+--------+------+-------+
| id   | name   | age  | BOOKS |
+------+--------+------+-------+
|    2 | Naruto |   18 |     2 |
|    6 | Robin  |   20 |     3 |
+------+--------+------+-------+

In the above query if we had used WHERE BOOKS > 1 instead of HAVING BOOKS>1 , then the SQL would have thrown some error.

Summary

  • HAVING is a replacement for the WHERE statement for GROUP BY clause.
  • HAVING is used to put conditions.

Leave a Reply

Your email address will not be published. Required fields are marked *