SQL Count with Null values

what about this:

SELECT Count(*) AS NationalParticipantsCount
FROM Participant
WHERE Country = 'Germany'

Select allOpen in new window

SELECT IsNull( Count(*), 0) AS NationalParticipantsCount, Country
FROM Participant
GROUP BY Country
HAVING Country = 'Germany'

Take what Angel suggested and wrap with IsNull.

SELECT IsNull(Count(*), 0) AS NationalParticipantsCount
FROM Participant
WHERE Country = 'Germany'

Select allOpen in new window

Or mastoo's suggestion.  Didn't see your post until mine went through, mastoo. :)

Never mind mine :-)

you could UNION it with another sql statement that returns a blank row or a 0 when that condition is met.
That way you get a 0 returned when the table is empty or that group/having condition is not met and when it is, the UNION sql never succeeds.

Hey guys, thanks for your replies.

Angelll's solution works, but I need the country column in the result.
And mastoo's solution doesn't work for me.
The ISNULL function doesn't hit because the values aren't NULL they are actually empty.

And as far as the IsNull goes, the COUNT and other aggregates usually optimize out NULLs anyway -- I just forgot that when posted earlier.  What is happening is that if you don't have any records, then you can't display country using the column name.  You have to use a literal.  If you have mutiple values, I would use a UNION as suggested earlier.

Thanks mwvisa1,

that was the solution

You are welcome.