Solved

SQL Count with Null values

Posted on 2008-10-13
11
1,180 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
  • 5
  • 2
  • 2
  • +2
11 Comments
 
LVL 142

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 59

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 59

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 59

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 59

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 59

Expert Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

823 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