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 helpSELECT 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_CodeGROUP BY SPGlobalLookups.dbo.County_LKP.County_Name, Filtered.Web_DescORDER BY SPGlobalLookups.dbo.County_LKP.County_NameDaveHelixpoint Web Developmenthttp://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 zeroDaveHelixpoint Web Developmenthttp://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 clleft 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_codegroup by cl.county_name ,f.web_descorder by cl.county_name Be One with the OptimizerTG |
|
|
helixpoint
Constraint Violating Yak Guru
291 Posts |
Posted - 2010-11-08 : 16:22:13
|
No dice TGDaveHelixpoint Web Developmenthttp://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 OptimizerTG |
|
|
X002548
Not Just a Number
15586 Posts |
|
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_CodeGROUP BY SPGlobalLookups.dbo.County_LKP.County_Name, Filtered.Web_DescORDER 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. |
|
|
|