SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 trying to get counts
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

helixpoint
Constraint Violating Yak Guru

279 Posts

Posted - 11/05/2010 :  15:37:10  Show Profile  Reply with Quote
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

USA
37142 Posts

Posted - 11/05/2010 :  15:45:07  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 11/05/2010 :  16:16:13  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Instead of using "*" in the count, reference an outer column instead.

COUNT(SomeOuterColumnNameHere)



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

helixpoint
Constraint Violating Yak Guru

279 Posts

Posted - 11/05/2010 :  16:21:00  Show Profile  Reply with Quote
I tried that Peso. No luck. I am not sure what else to give you

Dave
Helixpoint Web Development
http://www.helixpoint.com
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 11/05/2010 :  16:23:49  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 11/08/2010 :  04:59:57  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Change WHERE to AND and see if you get 0 counts

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

helixpoint
Constraint Violating Yak Guru

279 Posts

Posted - 11/08/2010 :  08:14:16  Show Profile  Reply with Quote
That did not work madhivanan

Dave
Helixpoint Web Development
http://www.helixpoint.com
Go to Top of Page

TimSman
Posting Yak Master

USA
127 Posts

Posted - 11/08/2010 :  09:13:23  Show Profile  Reply with Quote
So, you want everything from Web_Desc that has a count of 0, or you just want to show 0 for the count column?
Go to Top of Page

helixpoint
Constraint Violating Yak Guru

279 Posts

Posted - 11/08/2010 :  09:55:35  Show Profile  Reply with Quote
I want to show a 0 if there are no offenders for the county/webtype

Dave
Helixpoint Web Development
http://www.helixpoint.com
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 11/10/2010 :  03:26:18  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 11/10/2010 :  03:34:03  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

helixpoint
Constraint Violating Yak Guru

279 Posts

Posted - 11/10/2010 :  14:58:03  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 11/11/2010 :  00:38:09  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

EugeneLim11
Posting Yak Master

167 Posts

Posted - 11/11/2010 :  04:05:30  Show Profile  Visit EugeneLim11's Homepage  Reply with Quote
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 :)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000