Solved

SQL Count with Null values

Posted on 2008-10-13
11
1,176 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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

760 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

19 Experts available now in Live!

Get 1:1 Help Now