Trying to get counts with grouping

Question

Post reply

Trying to get counts with grouping

c-davifetr

SSC Rookie

Points: 25
More actions
November 9, 2010 at 6:26 am

#388190

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

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Award-winningly sketchy spec, but here goes:

SELECT

offenders = Agg.OffendersOfThisTypeInThisCounty,

m.County_Name,

m.Web_Desc,

'1' AS 'Address_Type'

FROM (

SELECT c.County_Code, c.County_Name, t.Offender_Type_LKP_ID, t.Web_Desc

FROM SPGlobalLookups.dbo.County_LKP c

CROSS JOIN dbo.Offender_Type_LKP t

) m

LEFT JOIN (

SELECT o.Offender_Type_LKP_ID, a.County_Code, OffendersOfThisTypeInThisCounty = COUNT(*)

FROM dbo.Offender o

INNER JOIN dbo.OffenderAddress a ON o.OffenderID = a.offenderid

GROUP BY o.Offender_Type_LKP_ID, a.County_Code

) Agg ON Agg.County_Code = m.County_Code AND Agg.Offender_Type_LKP_ID = m.Offender_Type_LKP_ID

Note that this is untested. See my sig for notes for new posters.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

c-davifetr SSC Rookie Points: 25 More actions · Answer 2

This seems to be close. I need to group on t.Web_Desc. Notice I have 4 Out-of-State Offender

45ArmstrongLifetime Offender1

NULLArmstrongOut-of-State Offender1

4ArmstrongOut-of-State Offender1

NULLArmstrongOut-of-State Offender1

14ArmstrongOut-of-State Offender 1

NULLArmstrongSexual Violent Predator1

18ArmstrongTen Year Offender1

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

c-davifetr (11/9/2010)
This seems to be close. I need to group on t.Web_Desc. Notice I have 4 Out-of-State Offender
45ArmstrongLifetime Offender1
NULLArmstrongOut-of-State Offender1
4ArmstrongOut-of-State Offender1
NULLArmstrongOut-of-State Offender1
14ArmstrongOut-of-State Offender 1
NULLArmstrongSexual Violent Predator1
18ArmstrongTen Year Offender1

Sorry, don't have time to make guesses. If you can provide scripts to create and populate tables then I'll be right back.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

c-davifetr SSC Rookie Points: 25 More actions · Answer 4

Unfortunately I can’t put the data out. Too sensitive

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

c-davifetr (11/9/2010)
Unfortunately I can’t put the data out. Too sensitive

Wise move - but you could make some up.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden