Explain the Group By with a filter and Count on filtered column

[Rx_]

ID_Wells is the FK for this table.
I was a little surprised.
The ID_Wells grouped by and had the Count of how many HZ_Type_Num were in each ID_Well.
So, I picked one with a count of 5 and filtered the source table Wells_Lease_DirHz.
To my surprise, one of the 5 records didn't have the filter 1,4,5.
Found more cases of this. It was counting all the records if any of the other records had a 1,4,5.

For my purpose to test - Does this ID_Wells have at least 1 assocated Dir_hz with a 1,4,5? This will work.
But, I was surprised it counted all of them.

Can someone explain why?
Or can somone tell me a better way to filter a list of 0 to many ID_Wells that have at least one related Dir_Hz with a 1,4,5

SELECT Wells_Lease_DirHz.ID_Wells, Count(Wells_Lease_DirHz.HZ_Type_Num) AS CountOfHZ_Type_Num
FROM Wells_Lease_DirHz
GROUP BY Wells_Lease_DirHz.ID_Wells
HAVING (((Count(Wells_Lease_DirHz.HZ_Type_Num)) In (1,4,5)))
ORDER BY Wells_Lease_DirHz.ID_Wells;

B.T.W. the exact same query on MS SQL Server returned the same thing.

OK, I think a very dim light came on.
After looking at the results, even the ID_Wells with a count of 1 had something other than 1,4,5 (in above sample)

By using the WHERE clause - the group-by seems to only return the ID_Wells I needed.
SELECT ID_Wells, COUNT(HZ_Type_Num) AS Expr1
FROM dbo.Wells_Lease_DirHz
WHERE (HZ_Type_Num IN (1, 4, 5))
GROUP BY ID_Wells
ORDER BY ID_Wells

Going one step beyond in SQL - this returned the proper counts! OK, this is what a 11 1/2 hour day does to you.
Mom, don't let your kids grow up to be programmers.

SELECT TOP (100) PERCENT ID_Wells, COUNT(HZ_Type_Num) AS Expr1
FROM dbo.Wells_Lease_DirHz
WHERE (HZ_Type_Num IN (1, 4, 5))
GROUP BY ID_Wells
ORDER BY ID_Wells

 

[Brianwarnock]

You have just illustrated the difference in filtering before and after grouping, it is important to use the correct technique for your requirements.

Brian

 

[plog]

HAVING works on the aggregate level (SUM([Field]), COUNT([Field]), etc.).

WHERE works on the individual record level ([Field]).

Depending at what level you want to apply criteria is what clause you use.

 

[Brianwarnock]

HAVING works on the aggregate level (SUM([Field]), COUNT([Field]), etc.).

WHERE works on the individual record level ([Field]).

Depending at what level you want to apply criteria is what clause you use.

Isn't that what I said?

Brian

 

[plog]

You have just illustrated the difference in filtering before and after grouping, it is important to use the correct technique for your requirements.

Not explicitly.

 

[Brianwarnock]

Rx_ is not a newbie! I didn't think it necessary to hold his hand! plus I thought that he had solved and understood his problem when he said that a light had come on even if it was a dim one.

I was merely adding a clarification for all readers that his problem indicated the difference between a Where and a Having clause, still some will benefit from further explanation but in that case shouldn't you tell them how to achieve each in the design grid.

Brian

 

[Rx_]

Due to the time zone difference and working until sunset, I did figure it out.
And both answers to my question of *why* are correct and helpful.
I am guilty of being verbose in a way that helps other newbies ... or helping myself look something back up during my 'senior moment'. LOL
I appreiacte all answers and appreciate Brianwarnock's recgonition that "Rx_ is not a newbie!". In my book, that counts as a complement.