Counts by groups

I expect to get a record below with a count of 0 (and I do), but when I take the comments out (--) of lines 1 & 6 I don't understand why I get no records at all. I need to be able to see all teams in EvalAnswers even if none of the records satisfies the where clause.

1select Count(*)as cnt--, TeamID2from EvalAnswers3where CoID=@.CoID4and EvaluatorID=@.EvaluatorID5and (Scr0=0 and Sugg0is NULL)6--group by TeamID7

BBradshaw:

even if none of the records satisfies the where clause.

- Why have the where clause then? A "Where" clause (as you know) filters out anything that doesn't match it... so what do you really want.. please explain what you are trying to query in human terms, not code terms, and I can help you better.

Thanks,

|||

Obviously, as shown by the commented-out query, SQL is designed to return a count of zero to tell me none of a specific filtered/matching kind of record currently exists, which is exactly what I want. I now want to show an itemized listing of all teams within a given company with their counts of existing filtered/matching records for each,even/especially if none exists. I know I can use the ALL construct on my Group By clause to get the zero counts for non-matching records, but that seems to give me too much (all companies, not just the one specified).

So, this morning I plan to play with the ALL and HAVING clauses, and expect to get what I need that way. However, I may be going about this all wrong, so any help would be appreciated.

|||

Please let me know if you see anything wrong with this code. It seems to work correctly for what I want, but I've tested in on only one scenario. I think the ALL and HAVING worked, rearranging the other parameters.

I appreciate your comments and help.

1selectCoID, TeamID, Count(*)as incomplete2fromEvalAnswers3whereScr0=0 and Sugg0is NULL4group byallCoID, TeamID, EvaluatorID5 havingCoID=@.CoID and EvaluatorID=@.EvaluatorID

|||I'm glad to see you've found the right way, you really need GROUP BY ALL in this case. This is why we need "GROUP BY ALL", it's by design, not a flaw of T-SQL.