SQL Having Clause

Posted in /   /  

SQL Having Clause

Vinay Khatri
Last updated on June 9, 2022

    Like the WHERE clause, the HAVING clause in SQL is used to put a condition on a statement. In SQL, we cannot use the WHERE statement with aggregated statements such as GROUP BY. Thus, we use the HAVING clause to set a condition. In this tutorial, we will be talking about the SQL HAVING clause. Also, you will learn the syntax of the HAVING clause and understand how to use it with a relevant example. So without further ado, let's get started!

    SQL HAVING Clause

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

    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 above query, consider the following 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 the number of books of those students who have borrowed more than 1 book from the library.

    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.

    Key Points to Remember

    The following are some of the key points that you should keep in mind while working with the Having clause:

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

    To Sum it Up

    This tutorial aims to help you understand the HAVING Clause in SQL, which is a replacement for the WHERE clause. Also, we have shared an example to help you understand the use of the HAVING clause in a SQL query. We hope that you have developed a clear understanding of the HAVING clause. In case you have any doubts or queries, you can let us know by sharing them in the comments section below. People are also reading:

    Leave a Comment on this Post

    0 Comments