Solved

SQL Count with Null values

Posted on 2008-10-13
11
1,185 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:arthrex
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +2
11 Comments
 
LVL 143

Expert Comment

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

Open in new window

0
 
LVL 21

Expert Comment

by:mastoo
ID: 22702530
SELECT IsNull( Count(*), 0) AS NationalParticipantsCount, Country
FROM Participant
GROUP BY Country
HAVING Country = 'Germany'
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22702533
Take what Angel suggested and wrap with IsNull.
SELECT IsNull(Count(*), 0) AS NationalParticipantsCount
FROM Participant
WHERE Country = 'Germany'

Open in new window

0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22702538
Or mastoo's suggestion.  Didn't see your post until mine went through, mastoo. :)
0
 
LVL 21

Expert Comment

by:mastoo
ID: 22702562
Never mind mine :-)
0
 
LVL 1

Expert Comment

by:crumber
ID: 22704132
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
 

Author Comment

by:arthrex
ID: 22710190
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
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 22710539
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22710558
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
 

Author Comment

by:arthrex
ID: 22711586
Thanks mwvisa1,

that was the solution
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22711940
You are welcome.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question