How do I get a count - 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

SQL Server 2005 Forums

Transact-SQL (2005)

How do I get a count

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2010-12-17 : 07:54:10

I need to get a count by webdesc on countyname

SELECT DISTINCT dbo.Offender_Type_LKP.Web_Desc AS webdesc, 'Philadelphia' AS countyname, 'Resident' AS addtype, dbo.Offender.OffenderID
FROM dbo.OffenderAddress INNER JOIN
dbo.Offender ON offender.offenderid = offenderaddress.offenderid AND dbo.OffenderAddress.County_Code = @CountyCode AND
dbo.OffenderAddress.IsCurrent = 1 AND dbo.OffenderAddress.Type <> 'Mailing' AND dbo.Offender.Active = 1 AND
dbo.Offender.DisplayOnPublicSite = 1 RIGHT OUTER JOIN
dbo.Offender_Type_LKP ON offender.offender_type_lkp_id = dbo.Offender_Type_LKP.Offender_Type_LKP_ID
WHERE (dbo.Offender_Type_LKP.Offender_Type_LKP_ID <> 9)

Here is the Data.
So for Philadelphia. I need to have
Lifetime Offender Count = 20
Sexually Violent Predator Count = 3
And so on

Lifetime Offender Philadelphia Resident 4362
Lifetime Offender Philadelphia Resident 4546
Lifetime Offender Philadelphia Resident 5033
Lifetime Offender Philadelphia Resident 8520
Lifetime Offender Philadelphia Resident 9456
Lifetime Offender Philadelphia Resident 9667
Lifetime Offender Philadelphia Resident 9875
Lifetime Offender Philadelphia Resident 10405
Lifetime Offender Philadelphia Resident 10570
Lifetime Offender Philadelphia Resident 10983
Lifetime Offender Philadelphia Resident 11353
Lifetime Offender Philadelphia Resident 12604
Lifetime Offender Philadelphia Resident 13654
Lifetime Offender Philadelphia Resident 13660
Lifetime Offender Philadelphia Resident 14331
Lifetime Offender Philadelphia Resident 15251
Lifetime Offender Philadelphia Resident 15266
Lifetime Offender Philadelphia Resident 15289
Lifetime Offender Philadelphia Resident 16238
Lifetime Offender Philadelphia Resident 16454
Out-of-State Offender Philadelphia Resident NULL
Out-of-State Offender Philadelphia Resident 814
Out-of-State Offender Philadelphia Resident 4256
Out-of-State Offender Philadelphia Resident 4970
Out-of-State Offender Philadelphia Resident 7963
Out-of-State Offender Philadelphia Resident 8351
Out-of-State Offender Philadelphia Resident 8591
Out-of-State Offender Philadelphia Resident 8754
Out-of-State Offender Philadelphia Resident 8826
Out-of-State Offender Philadelphia Resident 10162
Out-of-State Offender Philadelphia Resident 11041
Out-of-State Offender Philadelphia Resident 11363
Out-of-State Offender Philadelphia Resident 11407
Out-of-State Offender Philadelphia Resident 11540
Out-of-State Offender Philadelphia Resident 11828
Out-of-State Offender Philadelphia Resident 11942
Out-of-State Offender Philadelphia Resident 12787
Out-of-State Offender Philadelphia Resident 13278
Out-of-State Offender Philadelphia Resident 14048
Out-of-State Offender Philadelphia Resident 14187
Out-of-State Offender Philadelphia Resident 14787
Out-of-State Offender Philadelphia Resident 14995
Out-of-State Offender Philadelphia Resident 15011
Out-of-State Offender Philadelphia Resident 15422
Out-of-State Offender Philadelphia Resident 16079
Out-of-State Offender Philadelphia Resident 16486
Sexually Violent Predator Philadelphia Resident 9718
Sexually Violent Predator Philadelphia Resident 13864
Sexually Violent Predator Philadelphia Resident 14968
Ten Year Offender Philadelphia Resident 4386
Ten Year Offender Philadelphia Resident 11113
Ten Year Offender Philadelphia Resident 12083
Ten Year Offender Philadelphia Resident 13523
Ten Year Offender Philadelphia Resident 14072
Ten Year Offender Philadelphia Resident 15265
Ten Year Offender Philadelphia Resident 15816
Ten Year Offender Philadelphia Resident 15822
Ten Year Offender Philadelphia Resident 16090

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

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-12-17 : 12:58:40

I'm not clear what your output would look like. Can you elaborate?

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-18 : 08:01:15

Try:

SELECT dbo.Offender_Type_LKP.Web_Desc AS webdesc, 'Philadelphia' AS countyname, 'Resident' AS addtype , Count(1) as OffenderCount
FROM dbo.OffenderAddress INNER JOIN
dbo.Offender ON offender.offenderid = offenderaddress.offenderid AND dbo.OffenderAddress.County_Code = @CountyCode AND
dbo.OffenderAddress.IsCurrent = 1 AND dbo.OffenderAddress.Type <> 'Mailing' AND dbo.Offender.Active = 1 AND
dbo.Offender.DisplayOnPublicSite = 1 RIGHT OUTER JOIN
dbo.Offender_Type_LKP ON offender.offender_type_lkp_id = dbo.Offender_Type_LKP.Offender_Type_LKP_ID
WHERE (dbo.Offender_Type_LKP.Offender_Type_LKP_ID <> 9)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources