trying to get counts - 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

trying to get counts

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2010-11-05 : 15:37:10

Not sure if you can figure this out with the info I give you, but I am tring to get the counts. I am trying to do a RIGHT OUTER JOIN
on the Offender_Type_LKP table. I want to bring back all Web_Desc and get a 0 in the count.

SELECT COUNT(*) AS Expr1, dbo.Offender_Type_LKP.Web_Desc, SPGlobalLookups.dbo.County_LKP.County_Name
FROM dbo.Offender_Type_LKP LEFT OUTER JOIN
SPGlobalLookups.dbo.County_LKP INNER JOIN
dbo.OffenderEmployment ON SPGlobalLookups.dbo.County_LKP.County_Code = dbo.OffenderEmployment.County_Code INNER JOIN
dbo.Offender ON dbo.OffenderEmployment.OffenderId = dbo.Offender.OffenderID ON
dbo.Offender_Type_LKP.Offender_Type_LKP_ID = dbo.Offender.Offender_Type_LKP_ID
WHERE (dbo.OffenderEmployment.IsCurrent = 1)
GROUP BY SPGlobalLookups.dbo.County_LKP.County_Name, dbo.Offender_Type_LKP.Web_Desc
ORDER BY SPGlobalLookups.dbo.County_LKP.County_Name, dbo.Offender_Type_LKP.Web_Desc

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

tkizer
Almighty SQL Goddess

38200 Posts

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

You haven't provided enough information, but if you all want is 0 for the count then use 0 instead of count(*). I'm sure I am missing the point, but you haven't provided enough information to get the point across.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-11-05 : 16:16:13

Instead of using "*" in the count, reference an outer column instead.

COUNT(SomeOuterColumnNameHere)

N 56°04'39.26"
E 12°55'05.63"

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2010-11-05 : 16:21:00

I tried that Peso. No luck. I am not sure what else to give you

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-05 : 16:23:49

Data, we need sample data and expected result set.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-08 : 04:59:57

Change WHERE to AND and see if you get 0 counts

Madhivanan

Failing to plan is Planning to fail

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2010-11-08 : 08:14:16

That did not work madhivanan

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

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-08 : 09:13:23

So, you want everything from Web_Desc that has a count of 0, or you just want to show 0 for the count column?

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2010-11-08 : 09:55:35

I want to show a 0 if there are no offenders for the county/webtype

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-11-10 : 03:26:18

[code]SELECT COUNT(SPGlobalLookups.dbo.County_LKP.County_Code AS Expr1,
dbo.Offender_Type_LKP.Web_Desc,
SPGlobalLookups.dbo.County_LKP.County_Name
FROM dbo.OffenderEmployment
INNER JOIN dbo.Offender ON dbo.Offender.OffenderID = dbo.OffenderEmployment.OffenderId
INNER JOIN dbo.Offender_Type_LKP ON dbo.Offender_Type_LKP.Offender_Type_LKP_ID = dbo.Offender.Offender_Type_LKP_ID
LEFT JOIN SPGlobalLookups.dbo.County_LKP ON SPGlobalLookups.dbo.County_LKP.County_Code = dbo.OffenderEmployment.County_Code
WHERE dbo.OffenderEmployment.IsCurrent = 1
GROUP BY SPGlobalLookups.dbo.County_LKP.County_Name,
dbo.Offender_Type_LKP.Web_Desc
ORDER BY SPGlobalLookups.dbo.County_LKP.County_Name,
dbo.Offender_Type_LKP.Web_Desc[/code]

N 56°04'39.26"
E 12°55'05.63"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-10 : 03:34:03

quote:

Originally posted by Peso

SELECT		COUNT(SPGlobalLookups.dbo.County_LKP.County_Code) AS Expr1,
		dbo.Offender_Type_LKP.Web_Desc,
		SPGlobalLookups.dbo.County_LKP.County_Name
FROM		dbo.OffenderEmployment
INNER JOIN	dbo.Offender ON dbo.Offender.OffenderID = dbo.OffenderEmployment.OffenderId
INNER JOIN	dbo.Offender_Type_LKP ON dbo.Offender_Type_LKP.Offender_Type_LKP_ID = dbo.Offender.Offender_Type_LKP_ID
LEFT JOIN	SPGlobalLookups.dbo.County_LKP ON SPGlobalLookups.dbo.County_LKP.County_Code = dbo.OffenderEmployment.County_Code
WHERE		dbo.OffenderEmployment.IsCurrent = 1
GROUP BY	SPGlobalLookups.dbo.County_LKP.County_Name,
		dbo.Offender_Type_LKP.Web_Desc
ORDER BY	SPGlobalLookups.dbo.County_LKP.County_Name,
		dbo.Offender_Type_LKP.Web_Desc

N 56°04'39.26"
E 12°55'05.63"

No, you're never too old to Yak'n'Roll if you're too young to die.

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2010-11-10 : 14:58:03

Still not getting :0 Sexual Violent Predator Adams
for Adams

10 Lifetime Offender Adams
16 Out-of-State Offender Adams
11 Ten Year Offender Adams
203 Lifetime Offender Allegheny
86 Out-of-State Offender Allegheny
6 Sexual Violent Predator Allegheny
127 Ten Year Offender Allegheny

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-11-11 : 00:38:09

If someone hasn't told you to post some sample data before, you better do that right now.
I don't think anyone of us like to play guessing games.

N 56°04'39.26"
E 12°55'05.63"

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2010-11-11 : 04:05:30

It is best if you could tell us the tables, and post some sample data and what you hope to get. When you make it easy for us to help, we will be more inclined to help :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources