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
 help! how to get null values when using count

Author  Topic 

dave1816
Starting Member

9 Posts

Posted - 2013-11-04 : 11:06:01
I have this bit of code below and would like to bring back stat_code's which have no value as 0...anyway of achiving this??

Thanks

regards

Dave

select  stat_code, COUNT(stat_code)AS Stat_Count  
--INTO tableSroStatABD
from fs_sro
where sro_stat ='O' and whse = 'ABD'
group by stat_code
having stat_code in ('1 QUOTE WN', '2 PR & DEF', '2.1 PRTORD', '3 PRTS AVL', '4 SCHEDULD', '5 LABB JBC', '6 CSTB JBC', '7 WRKS CMP', '8 CSH APP', '9 TO B INV')
order by stat_code asc


D.ARNOLD

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-04 : 11:37:11
You need to create a list of stat_codes you want to consider and do a left join to that. Something like shown below:
SELECT  b.stat_code ,
COUNT(fs_sro.stat_code) AS Stat_Count
--INTO tableSroStatABD
FROM (VALUES ( '1 QUOTE WN'),( '2 PR & DEF'),( '2.1 PRTORD'),( '3 PRTS AVL'),
('4 SCHEDULD'),( '5 LABB JBC'),( '6 CSTB JBC'),( '7 WRKS CMP'),
('8 CSH APP'),( '9 TO B INV' )) AS b(stat_code)
LEFT JOIN fs_sro ON fs_sro.stat_Code = b.stat_code
WHERE sro_stat = 'O'
AND whse = 'ABD'
GROUP BY b.stat_code
ORDER BY b.stat_code ASC
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-04 : 14:04:35
quote:
Originally posted by James K

You need to create a list of stat_codes you want to consider and do a left join to that. Something like shown below:
SELECT  b.stat_code ,
COUNT(fs_sro.stat_code) AS Stat_Count
--INTO tableSroStatABD
FROM (VALUES ( '1 QUOTE WN'),( '2 PR & DEF'),( '2.1 PRTORD'),( '3 PRTS AVL'),
('4 SCHEDULD'),( '5 LABB JBC'),( '6 CSTB JBC'),( '7 WRKS CMP'),
('8 CSH APP'),( '9 TO B INV' )) AS b(stat_code)
LEFT JOIN fs_sro ON fs_sro.stat_Code = b.stat_code
WHERE sro_stat = 'O'
AND whse = 'ABD'
GROUP BY b.stat_code
ORDER BY b.stat_code ASC



This will still reduce it to an inner join and cause unmatched rows to be ignored I guess.
You might need to tweak it as


SELECT b.stat_code ,
COUNT(fs_sro.stat_code) AS Stat_Count
--INTO tableSroStatABD
FROM (VALUES ( '1 QUOTE WN'),( '2 PR & DEF'),( '2.1 PRTORD'),( '3 PRTS AVL'),
('4 SCHEDULD'),( '5 LABB JBC'),( '6 CSTB JBC'),( '7 WRKS CMP'),
('8 CSH APP'),( '9 TO B INV' )) AS b(stat_code)
LEFT JOIN fs_sro ON fs_sro.stat_Code = b.stat_code
AND sro_stat = 'O'
AND whse = 'ABD'
GROUP BY b.stat_code
ORDER BY b.stat_code ASC


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-04 : 14:05:25
Refer below for the explanation

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/criteria-on-outer-joined-tables.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

dave1816
Starting Member

9 Posts

Posted - 2013-11-05 : 11:35:28
Thank for the replies people, but I should of mentioned I have SQL 2005 so from values does not work...However I have a table listed with these status's which I have already tried but failed to work and is simlliar to James's code. visakh16 your script does work without using the where clause but it has comes back with a value of 1 instead of 0 :( no idea how to alter this

regards

D.ARNOLD
Go to Top of Page

dave1816
Starting Member

9 Posts

Posted - 2013-11-05 : 11:41:21
spoke to soon :) solved with

select sc.stat_code, count(sro.stat_code)AS Stat_Count  from fs_stat_code SC 
left outer join fs_sro SRO on SC.stat_code = SRO.stat_code
and SRO.sro_stat ='O' and SRO.whse = 'ABD'
group by sc.stat_code
having sc.stat_code in ('1 QUOTE WN', '2 PR & DEF', '2.1 PRTORD', '3 PRTS AVL', '4 SCHEDULD', '5 LABB JBC', '6 CSTB JBC', '7 WRKS CMP', '8 CSH APP', '9 TO B INV')
order by sc.stat_code asc


thanks everyone

D.ARNOLD
Go to Top of Page
   

- Advertisement -