March 5, 2012 at 2:54 pm
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
This is my desired result set:
White Belt 223
Green Belt 107
Brown Belt 89
Can anyone see what I'm doing wrong and help clean it up?
March 5, 2012 at 3:07 pm
Somethin more like this?
Select
approxrank,
Count (approxrank) as BeltCount
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
March 5, 2012 at 3:12 pm
Please refer to the following article on how best to post sample data.
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Second, your sample data doesn't match your expected results. Your sample data contains one entry for a black belt, yet black belt does not appear in your expected results. Conversely, your expected results contain entries for Green, White, and Brown belts, but your sample data contains no entries for any of these belts. The point of providing both sample data and expected results is so that people can run their query on the sample data and see if it matches the expected results. If there is no way to derive the expected results from the sample data, it defeats this purpose.
Your sample data only contains one row for each table. You should provide a sample that is large enough to be representative of your data while being small enough to not be unwieldy. One row is almost never sufficient to provide a representative sample.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 5, 2012 at 3:40 pm
Lynn Pettis (3/5/2012)
Somethin more like this?
Select
approxrank,
Count (approxrank) as BeltCount
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
Thanks Lynn, that hit the spot! Switched the final columname as follows, added a little order to chaos, and I think it did the trick!
Select
approxrank as BeltColour,
Count (approxrank) as BeltCount
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
Order by BeltCount desc
Results....
BeltColour BeltCount
White 224
Orange 103
Orange/ Black Stripe71
White / Green Stripe60
March 5, 2012 at 3:52 pm
Drew, thanks for the heads-up. As a man who appreciates clarity I am the last to complain when I'm fairly called out for being unclear.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply