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 | Status | |
| 1 | Tim Robbins | +44 54 443-4434 | London | [email protected] | Active |
| 2 | James Chris | +44 54 498-3476 | Birmingham | [email protected] | Active |
| 3 | Kevin Sputnik | +44 54 487-6987 | Manchester | [email protected] | Inactive |
| 4 | Richard Butler | +44 54 422-2345 | Birmingham | [email protected] | Active |
| 5 | David McGregor | +44 54 413-0989 | Cardiff | [email protected] | Active |
| 6 | Rita Johns | +44 55 453-4534 | London | [email protected] | 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
- Architecture of DBMS
- CODDS RULE DBMS
- Database Models in DBMS
- Relational DBMS Concepts
- Keys and Types of keys in Database
- Database Normalization
- Generalization, Specialization and Aggregation Concepts in DBMS
- ERD Diagram Tutorial with Examples in DBMS
- Introduction to SQL
- How to Create Query in SQL, Create Table, Delete Table and User Insert Info Statements
- Alter Query Command in SQL, Add, Modify and Drop Column in Table
- TCL Commands in SQL
- Truncate Query, Drop and Rename Query in SQL
- All DML Statement in SQL, Select Statement, Delete, Insert and Update Statement
- Data Control Language DCL Revoke and Grant Command in SQL
- Select Statement or Select Query in SQL Explained with Examples
- Distinct Keyword Explained in SQL
- WHERE Statement or WHERE Clause in SQL Statement Explained
- AND & OR Operators in SQL
- LIKE Operator in SQL
- ORDER BY Clause Sorting Explained in SQL
- Group By Clause in SQL
- SQL Constraints
- SQL Aggregate Functions
- SQL Aliases Use and Purpose in SQL
- SQL Joins Types, Use and Purpose
- SQL Sequence Syntax and Use with Examples
- SQL View
- SET Operation in SQL, UNION, UNION ALL, Intersect and Minus