Solved

SQL Count with Null values

Posted on 2008-10-13
11
1,178 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
 
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

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. …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

895 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now