WHERE vs HAVING

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: