SQL Server - HAVING

In SQL Server we use WHERE keyword to put condition on statement.
But when we use the GROUP BY keyword then to apply filter we have to use the HAVING keyword.

Let consider below list of student with there class, marks, city and contact details.

HAVING with GROUP BY in SQL Server

Find out the count of student from each city and list only cities having more than two students.
Let see below example.

Example

Copy

SELECT COUNT (ID) AS 'StudentCount' , City
FROM tblStudent
GROUP BY City
HAVING COUNT (ID) > 2

Output

Use HAVING and WHERE in same statement in SQL Server

Let see how filter HAVING and WHERE clause work on to filter data.

Example

Copy

SELECT City , Class, COUNT (ID) AS 'StudentCount'
FROM tblStudent WHERE City= 'Mumbai'
GROUP BY City, Class
HAVING COUNT (ID) <= 2
ORDER BY City

Output

Question : What is difference between WHERE and HAVING clause in SQL Server? OR
When to use the WHERE and when to use HAVING in SQL Server ?

Difference : When we filter data on normal DML (SELECT, INSERT, UPDATE, DELETE) statements then we use the WHERE clause. HAVING clause we use only after GROUP BY clause, when we want to set filter on GROUP BY result set we can do by HAVING clause.

What to use when : If you observe the above statement we use the WHERE clause to filter data , after that on filter data we use the GROUP BY clause and to filer grouped data we use the HAVING clause.
It mean we filter data on normal statement using WHERE clause and when we use GROUP BY clause and we want to filter them then we use HAVING clause instead of WHERE clause.