Need to get the null values - 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

General SQL Server Forums

New to SQL Server Programming

Need to get the null values

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2010-11-08 : 15:05:45

I am trying to group on a few tables to get the counts, but when there is a Null, I get nothing. I need to bring a 0 back. I posted a different SQL before. Can anyone help

SELECT ISNULL(COUNT(Filtered.Web_Desc), 0) AS offenders, SPGlobalLookups.dbo.County_LKP.County_Name, Filtered.Web_Desc,
'1' AS 'Address_Type'
FROM SPGlobalLookups.dbo.County_LKP RIGHT OUTER JOIN
(SELECT offender.offenderid, dbo.OffenderAddress.County_Code, dbo.Offender_Type_LKP.Web_Desc
FROM dbo.Offender INNER JOIN
dbo.OffenderAddress ON Offender.OffenderID = offenderaddress.offenderid RIGHT OUTER JOIN
dbo.Offender_Type_LKP ON Offender.Offender_Type_LKP_ID = dbo.Offender_Type_LKP.Offender_Type_LKP_ID
GROUP BY offender.offenderid, Offender_Type_LKP.Web_Desc, dbo.OffenderAddress.County_Code) AS Filtered ON
Filtered.County_Code = SPGlobalLookups.dbo.County_LKP.County_Code
GROUP BY SPGlobalLookups.dbo.County_LKP.County_Name, Filtered.Web_Desc
ORDER BY SPGlobalLookups.dbo.County_LKP.County_Name

Dave
Helixpoint Web Development
http://www.helixpoint.com

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-08 : 15:20:08

Where are the NULL fields?

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2010-11-08 : 15:39:12

The count of offenders in each county,. I net nothing back. No records for that county. I need to return a zero

Dave
Helixpoint Web Development
http://www.helixpoint.com

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-11-08 : 16:15:41

try this:


select count(f.web_desc) as offenders
	,cl.county_name
	,f.web_desc
	,'1' as [Address_type]
from	SPGlobalLookups.dbo.County_LKP cl
left outer join (
		select o.offenderid
			,oa.county_code
			,ol.web_desc
		from	dbo.offender o
		inner join offenderAddress oa
			on oa.offenderid = o.offenderid
		left outer join offender_type_lkp ol
			on ol.offender_type_lkp_id = o.offender_type_lkp_id
		group by o.offenderid
			,ol.web_desc
			,oa.county_code
	)  f
		on f.county_code = cl.county_code
group by cl.county_name
	,f.web_desc
order by cl.county_name

Be One with the Optimizer
TG

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2010-11-08 : 16:22:13

No dice TG

Dave
Helixpoint Web Development
http://www.helixpoint.com

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-11-08 : 16:50:45

what is the result? Are you getting back a row for each county but with a NULL for [offenders]? Or are you not getting back a row for each county? Or an error?

Be One with the Optimizer
TG

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-08 : 17:29:53

Dave,

Do your clients see this?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

niechen861102
Starting Member

9 Posts

Posted - 2010-11-08 : 21:23:29

Are you getting back a row for each county but with a NULL for [offenders]? Or are you not getting back a row for each county? Or an error? http://www.saleuggsbootsuk.com/

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2010-11-09 : 07:04:12

I am trying to get the counts back for all offenders for each type and county. The problem is that I do not get a record if there are no offenders for that type/country. I need to bring back a record of 0. Notice below that Allegheny has 4 types. Armstrong does not. I need to also bring back a row for Arrmstrong like this... "0 Armstrong Sexual Violent Predator 1"

427 Allegheny Lifetime Offender 1
165 Allegheny Out-of-State Offender 1
18 Allegheny Sexual Violent Predator 1
284 Allegheny Ten Year Offender 1
22 Armstrong Lifetime Offender 1
9 Armstrong Out-of-State Offender 1
9 Armstrong Ten Year Offender 1

SELECT ISNULL(COUNT(Filtered.Web_Desc), 0) AS offenders, SPGlobalLookups.dbo.County_LKP.County_Name, Filtered.Web_Desc,
'1' AS 'Address_Type'
FROM SPGlobalLookups.dbo.County_LKP RIGHT OUTER JOIN
(SELECT offender.offenderid, dbo.OffenderAddress.County_Code, dbo.Offender_Type_LKP.Web_Desc
FROM dbo.Offender INNER JOIN
dbo.OffenderAddress ON Offender.OffenderID = offenderaddress.offenderid RIGHT OUTER JOIN
dbo.Offender_Type_LKP ON Offender.Offender_Type_LKP_ID = dbo.Offender_Type_LKP.Offender_Type_LKP_ID
GROUP BY offender.offenderid, Offender_Type_LKP.Web_Desc, dbo.OffenderAddress.County_Code) AS Filtered ON
Filtered.County_Code = SPGlobalLookups.dbo.County_LKP.County_Code
GROUP BY SPGlobalLookups.dbo.County_LKP.County_Name, Filtered.Web_Desc
ORDER BY SPGlobalLookups.dbo.County_LKP.County_Name

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-09 : 09:19:18

I think you need to rearrange your logic. I would write it out more, but it's difficult without seeing table structures and data.

I would start by getting all the offender types and the counts for each.

The right joins could be replaced with left joins. It looks like the base table here is Offender, so I would start with that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources