Generic statistics syntax - 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

Generic statistics syntax

Author

Topic

VBScab
Starting Member

5 Posts

Posted - 2006-05-17 : 13:19:54

I have a bunch of tables for my basketball club's d/b. I want to be able to interrogate them to build a web page showing player stats. For example, I want a table showing the top 5 scorers, top 5 3-point scorers, etc. What's got me foxed is how to join it all up and then sort it into the top 5 order. The main tables in question are T_Member, T_Fixture, T_Season and T_FixtureTeam:

T_Member:
MemberNo, FirstName, LastName, (etc)

T_Fixture:
FixtureNo, SeasonNo, MatchDate, (etc)

T_Season:
SeasoNo, SeasonDescription, SeasonStartDate, SeasonEndDate, CurrentSeason

T_FixtureTeam:
FixtureNo, MemberNo, 2Points, 3Points,Fouls,FreeShotAttempts,FreeShotsHit,Assists

Where you see the same field name, that field data is common for all tables that it appears in. In other words, for example, the T_FixtureTeam 'FixtureNo' and 'MemberNo' fields are made up of 'T_Fixture.FixtureNo' and 'T_Member.MemberNo' respectively.

Ready?
The user will select a season at the top of the page (although there's a default to the current season (CurrentSeason is a Boolean field). The query needs to:
- Find all fixtures in that season
- Find all members who played in those fixtures
- Find the top 5 3-point scorers (or whatever stat I'm gathering) for those matches
- Sort the resulting data

I figure I need a JOIN on MemberNo but, as I say, the rest of it has my head spinning.

TIA

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-05-18 : 02:13:00

This should put you on track

SELECT TOP 5 M.MemberNo, M.FirstName, M.LastName, SUM(3Points)
FROM T_Member M
INNER JOIN T_FixtureTeam T
ON T.MemberNo = M.MemberNo
INNER JOIN T_Fixture F
ON F.FixtureNo = T.FixtureNo
INNER JOIN T_Season S
ON S.SeasoNo = F.SeasonNo
WHERE CurrentSeason = 1
GROUP BY M.MemberNo, M.FirstName, M.LastName
ORDER BY SUM(3Points) DESC

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources