SQL Help - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Help

Author

Topic

jrockfl
Posting Yak Master

223 Posts

Posted - 2005-02-16 : 15:41:56

I'm trying to return all of the slspsn_no listed, If they dont have any results, I'd like to return 0...so it displays like...

101 0
102 0
103 2

etc...

SELECT Slspsn_No, CASE WHEN COUNT(Slspsn_no) = NULL THEN 0 END AS previousday
FROM tblPoints
WHERE (sudate BETWEEN '02/02/2005' AND '02/02/2005')
AND (Slspsn_No IN ('101', '102', '103', '104', '105', '106', '107', '108', '110', '114', '115', '116','117'))
GROUP BY Slspsn_No

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-16 : 15:51:34

Maybe something like this...?


Declare @slsPsnList table (slspsn_No int)
Insert Into @slsPsnList
Select '101' Union Select '102' Union Select '103' Union Select '104' Union Select '105' Union Select '106' Union Select '107' 
Union Select '108' Union Select '110' Union Select '114' Union Select '115' Union Select '116' Union Select '117'

SELECT A.Slspsn_No, COUNT(B.Slspsn_no)0 AS previousday
FROM @slsPsnList A
Left Join (Select * From tblPoints WHERE sudate BETWEEN '02/02/2005' AND '02/02/2005') B
On A.slspsn_No = B.slspsn_No
GROUP BY A.Slspsn_No

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-16 : 16:01:03

count(<column>) will return always return 0 rather than NULL. That's why Corey didn't need your case statement. But you might want to lookup the function "isNull" from BOL anyway.

Be One with the Optimizer
TG

jrockfl
Posting Yak Master

223 Posts

Posted - 2005-02-16 : 16:03:35

Yes, I tried IsNull too...and its not returning the results I need...this is what I used

SELECT Slspsn_No, IsNull(COUNT(Slspsn_No), 0) AS previousday
FROM tblPoints
WHERE (sudate BETWEEN '02/02/2005' AND '02/02/2005')
AND (Slspsn_No IN ('101', '102', '103', '104', '105', '106', '107', '108', '110', '114', '115', '116','117'))
GROUP BY Slspsn_No

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-16 : 16:18:41

This statement:
SELECT Slspsn_No, IsNull(COUNT(Slspsn_No), 0) AS previousday

Will give the same result as this statement:
SELECT Slspsn_No, COUNT(Slspsn_No) AS previousday

If Corey's statement didn't work for you, what are the results you need?

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources