Hugo Kornelis (8/31/2010)

---

webrunner (8/31/2010)

---

I guess I was focused too much on the COUNT() function itself and what it returns instead of the SELECT query as a whole and the fact that the whole query may come up with a set of 0 rows that satisfy the conditions listed. Is that on the right track?

Yes, it is.

Suppose I had used this query:

SELECT COUNT(*)

FROM #QotD

WHERE Col2 <> 4

GROUP BY Col1

HAVING MAX(Col2) < 1;

I guess a lot more poeple would have correctly predicted the empty result set. We are all used to metally checking off which groups satisfy the HAVING condition if both GROUP BY and HAVING are in a query. We are also used to expecting a single row result set when aggregates are used without GROUP BY. We are not programmed to do any additional checking on a HAVING without GROUP BY, because this is a very seldom-used construction.

That is what I based this question on - those two automatisms for two "normal" constructions, that lead us to incorrect expectations when the ingredients are mixed in an unusual way.