Return 0 instead of No Results

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Return 0 instead of No Results

Author

Topic

swims01
Yak Posting Veteran

59 Posts

Posted - 2009-06-05 : 14:47:56

I have a simple query that many times does not return any results. Instead of returning nothing I'd like it to return 0. This is all over google but they're all confusing.

My query is very simple...

SELECT club, 
	count(id) as reinstatements 
	from tbl
	where reinstatement_date >= @Day 
	group by club

Many people were talking about doing some kind of join on the same table but that seemed kind of confusing.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-06-05 : 14:51:29

You mean for a given club you have no rows so that club doesn't show in resutls? Is that the problem you are trying to solve?

Be One with the Optimizer
TG

swims01
Yak Posting Veteran

59 Posts

Posted - 2009-06-05 : 14:56:28

In my example if any of the clubs returns 1 or more then it will give me good results.

ex.
club1 0
club2 0
club3 6

But when all clubs return 0 then it doesn't return any results. I'd like them to all say '0' in that case.

ex.
club1 0
club2 0
club3 0

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-06-05 : 15:02:39

Ok you need to understand the concept of an OUTER JOIN and the consequences of no results that meet your criteria. Take a look at these queries:


set nocount on
declare @club table (clubid int identity(1,1), clubName varchar(10))
insert @club (clubName) values ('club 1')
insert @club (clubName) values ('club 2')
insert @club (clubName) values ('club 3')

declare @tbl table (clubid int, dt datetime)
insert @tbl
select 1, getdate() union all
select 1, getdate()-1 union all
select 1, getdate()-2 union all
select 2, getdate()


select clubid, count(*) as cnt from @tbl group by clubid

select clubName, count(dt) as cnt
from   @club c
left outer join @tbl t
       on t.clubid = c.clubid
group by clubName
 

OUTPUT:
clubid      cnt
----------- -----------
1           3
2           1

clubName   cnt
---------- -----------
club 1     3
club 2     1
club 3     0

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-06-05 : 15:04:52

If you want all the possible clubs reported (even when no rows exist that match your criteria) then you need to provide a seperate table with a complete list of the clubs you want reported. Then you LEFT OUTER JOIN to your data table and count those rows for each club. If no rows exist then you'll get your desired "0" count.

Be One with the Optimizer
TG

swims01
Yak Posting Veteran

59 Posts

Posted - 2009-06-05 : 15:14:52

Thanks TG. I'm testing it out now. My initial results is that nothing has changed but I understand what you're saying - I just need to apply it now.

Does the same logic apply when my clubs are in a table and my data is 'within' a view?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-06-05 : 15:17:08

Yes, same applies.

EDIT:
post your actual code if you still can't get it

Be One with the Optimizer
TG

swims01
Yak Posting Veteran

59 Posts

Posted - 2009-06-05 : 15:22:38

Yeah, I tried different combinations with no luck...
*edited*

SELECT     f.vcFacilityName, COUNT(r.iContractId) AS reinstatements
FROM         vwReactivatedContracts R INNER JOIN
                      tblFacility F ON r.vcHomeFacilityId = f.vcFacilityID
WHERE     (r.reactivation_date >= @Day)
GROUP BY f.vcFacilityName

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-06-05 : 15:33:41

I think you missed the point of OUTER join. And your current combination is backwards. you need the vcFacitityName table to be the first table then LEFT OUTER join to the one you want to count from:


select f.vcFacilityName
       ,COUNT(r.iContractId) AS reinstatements
from   tblFacility F
LEFT OUTER JOIN vwReactivatedContracts R
       on r.vcHomeFacilityId = f.vcFacilityID
       and r.reactivation_date >= @Day
group by f.vcFacilityName

The only other weird thing is that I put the @day condition in the JOIN criteria since the criteria filters your OUTER table.

Be One with the Optimizer
TG

swims01
Yak Posting Veteran

59 Posts

Posted - 2009-06-05 : 15:40:24

That was my original query which works when a result is found. I didn't want to post my attempts at the OUTER JOINS so as not to confuse you =)

But you got it! I can now apply this to a couple other scenarios. Many thanks TG!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-06-05 : 15:59:16

Great!
Just one word of caution about the "weird" thing I alluded to. If your filtering criteria is filtering column values from the first table then it can go in the WHERE clause to filter the whole set. But if your filtering criteria is filtering data from your OUTER JOINed table then you need to put it in the JOIN criteria. If it is in the WHERE clause then it has the effect of turning your outer join to an INNER join. It's a little tricky when you're first learning about it but the knowledge will make you a much better developer.

Be One with the Optimizer
TG

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-06 : 13:12:28

It's a strange appraoch writing a post on a forum telling us the query doesn't work and at the end it's revelead that the query posted is not the one giving problem.
The query giving problem is another one, with an including JOIN.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources