SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 help! how to get null values when using count
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dave1816
Starting Member

United Kingdom
9 Posts

Posted - 11/04/2013 :  11:06:01  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 11/04/2013 :  11:37:11  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 11/04/2013 :  14:04:35  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 11/04/2013 :  14:05:25  Show Profile  Reply with Quote
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

United Kingdom
9 Posts

Posted - 11/05/2013 :  11:35:28  Show Profile  Reply with Quote
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

United Kingdom
9 Posts

Posted - 11/05/2013 :  11:41:21  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000