When first learning SQL one of the most confusing concepts to understand is when to use WHERE and when to use HAVING in a query. Both keywords are used to filter records so they are similar but each has a situation where it should be used.

Let’s set up a table with some example data:

create table dbo.WidgetInventory (
WidgetId int not null primary key,
WidgetColor varchar(10) not null,
IsUsed bit not null
);

insert into dbo.WidgetInventory(WidgetId, WidgetColor, IsUsed) 
values 
(1, 'Red', 1),
(2, 'Blue', 0),
(3, 'Red', 1),
(4, 'Yellow', 0),
(5, 'Yellow', 1),
(6, 'Red', 0);

So we have a supply of widgets in different colors. Some are used (IsUsed = 1) and others are new (IsUsed = 0).
Say that we need to write a query to identify used widgets in a particular color that we have more than one of.
Here’s a query to do that:

select WidgetColor, count(*) as WidgetCount
from dbo.WidgetInventory
where IsUsed = 1
group by WidgetColor
having count(*) > 1;

Since we only care about used widgets, we add the ‘where IsUsed = 1’ clause to limit our dataset to only used ones.
Then we group by the color and count the number of used widgets in each color. HAVING is used to operate on the aggregated value, in this case the count of the widgets.
The query result will be (‘Red’, 2) since Red is the only color for used widgets with more than one in stock.