SQL Count with Null values

Hi experts,

I have an sql query:
SELECT Count(*) AS NationalParticipantsCount, Country
FROM Participant
GROUP BY Country
HAVING Country = 'Germany'

Now the table Participant is empty in the beginning. So I get an empty response.
How do I modify my statement so it will display NationalParticipantsCount as "0" ?


Thanks a lot
arthrexAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
Something like this may be what you are looking for:
SELECT 'Germany' As Country, COUNT(*) As NationalParticipantsCount
FROM Participant 
WHERE Country = 'Germany'

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about this:
SELECT Count(*) AS NationalParticipantsCount
FROM Participant
WHERE Country = 'Germany'

Open in new window

0
 
mastooCommented:
SELECT IsNull( Count(*), 0) AS NationalParticipantsCount, Country
FROM Participant
GROUP BY Country
HAVING Country = 'Germany'
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Kevin CrossChief Technology OfficerCommented:
Take what Angel suggested and wrap with IsNull.
SELECT IsNull(Count(*), 0) AS NationalParticipantsCount
FROM Participant
WHERE Country = 'Germany'

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
Or mastoo's suggestion.  Didn't see your post until mine went through, mastoo. :)
0
 
mastooCommented:
Never mind mine :-)
0
 
crumberCommented:
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.
0
 
arthrexAuthor Commented:
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.

0
 
Kevin CrossChief Technology OfficerCommented:
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.
0
 
arthrexAuthor Commented:
Thanks mwvisa1,

that was the solution
0
 
Kevin CrossChief Technology OfficerCommented:
You are welcome.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.