Join not grouping as wanted - 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 2008 Forums

Transact-SQL (2008)

Join not grouping as wanted

Author

Topic

Jake Shelton
Yak Posting Veteran

74 Posts

Posted - 2012-03-05 : 16:58:56

Hi all,

I've got two tables I want to join and determine the distribution of belts held by martial arts students at their time of quitting (in 2009 or earlier), meta/sample data structured below

(tblname/colname/sample data):

PersonalFinance/ (ContactID, approxrank)/1234, Black Belt
PersonalProfiles (ID, LastAttended)/1234, 2010-01-01 00:00:00.000

Here's the code I've put together so far, but it only returns a single value;

Select Count (approxrank) as BeltRank
From PersonalFinance PF
Inner join PersonalProfiles PP
on PP.ID = PF.ContactID
Where PP.LastAttended <'2010-01-01 00:00:00.000'
Group by BeltRank

Desired data:

White Belt 223
Green Belt 107
Brown Belt 89

Can anyone see what I'm doing wrong and help clean it up?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2012-03-05 : 17:23:53

Select approxrank,count(*) as Beltrank
From PersonalFinance PF
Inner join PersonalProfiles PP
on PP.ID = PF.ContactID
Where PP.LastAttended <'2010-01-01 00:00:00.000'
Group by approxrank

your sample data is a little confusing.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources