Having Clause Explained in SQL

Having Clause

Having clause, in SQL, is used to specify conditions for Group By clause. This clause specify conditions to filter final results of within the Group.

The difference between WHERE clause and HAVING clause is that WHERE clause applies conditions on the selected columns whereas the HAVING clause applies conditions on the groups created by Group By clause.

Syntax:

SELECT *

FROM tbl_name

WHERE [conditions]

GROUP BY col_name_1, col_name_2, … , col_name_n

HAVING [conditions]

ORDER BY col_name

The HAVING clause is sub part of the GROUP BY clause and hence it has to follow the GROUP By clause and before the ORDER BY clause (if used).

For Example:

The current table view of the Customers table is as follows:

ID C_Name Contact_No City Email Status
1 Tim Robbins +44 54 443-4434 London tim@myemail.uk Active
2 James Chris +44 54 498-3476 Birmingham james@myemail.uk Active
3 Kevin Sputnik +44 54 487-6987 Manchester kevin@myemail.uk Inactive
4 Richard Butler +44 54 422-2345 Birmingham richard@myemail.uk Active
5 David McGregor +44 54 413-0989 Cardiff david@myemail.uk Active
6 Rita Johns +44 55 453-4534 London rita@myemail.uk Active

SELECT City, Count(City) as C_Count FROM Customers

WHERE STATUS = ‘ACTIVE’

GROUP BY CITY

HAVING C_Count >= 2 

City C_Count
Birmingham 2

In the above example, HAVING clauses enabled the query to filter only that record which resulted in a C_Count value greater than or equal to 2 which is correct in case of BIRMINGHAM.

More Related Articles For You

    DBMS Tutorial

    SQL and DBMS Interview Questions with Answers

    DBMS and SQL Practice Tests , Questions and Quizzes

Search

Shortcodes Ultimate

 
Follow Us
 
BGP