Path: csiph.com!usenet.pasdenom.info!news.albasani.net!newsfeed.freenet.ag!news2.euro.net!newsgate.cistron.nl!newsgate.news.xs4all.nl!post.news.xs4all.nl!not-for-mail
To: python-list@python.org
From: Dennis Lee Bieber <wlfraed@ix.netcom.com>
Subject: Re: Computing win/loss records in Python
Date: Sat, 25 Aug 2012 23:13:20 -0400
Organization: > Bestiaria Support Staff <
References: <0C181E0E-47FA-439C-8320-AC60AB8859AF@gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: list
Newsgroups: comp.lang.python
Message-ID: <mailman.3825.1345950818.4697.python-list@python.org>
Lines: 68
NNTP-Posting-Host: 2001:888:2000:d::a6
Xref: csiph.com comp.lang.python:27897

On Sat, 25 Aug 2012 22:20:05 -0400, Christopher McComas
<mccomas.chris@gmail.com> declaimed the following in
gmane.comp.python.general:

> Marshall
> Ohio State
> Kentucky
> Indiana

	Untested pseudo SQL

create table Teams
(
	ID	integer auto-increment primary key,
	name	char(?)
)
 
> Marshall,24,Ohio State,48,
> Kentucky,14,Indiana,10,
> Marshall,10,Indiana,7,
> Ohio State,28,Kentucky,10

create table Game
(
	ID	integer auto-increment primary key,
	gameDate	date
)

create table GameScore
(
	ID	integer auto-increment primary key,
	gameID	integer foreign key Game(ID),
	teamID		integer foreign key Team(ID),
	score		integer
)

{wins}
select t.name, cnt(t.name) from GameScore as gs
inner join Team as t
on t.ID = gs.teamID
inner join GameScore as gs2
on gs.gameID = gs2.gameID
group by t.name
having gs.score > gs2.score

{I'm out of practice, so the constraints on group by/having may not be
valid}

{losses}
...
having gs.score < gs2.score

{ties/draws}
...
having gs.score = gs2.score
 
	GameScore avoids having to explicitlly order the raw results to, for
example, always put the winner in front as in

ID, date, winningTeamID, winningScore, losingTeamID, losingScore

{to get the number of games a team played in would require obtaining the
count of records with the team ID in winningTeamID and adding the count
of records with the same team ID in losingTeamID}
-- 
	Wulfraed                 Dennis Lee Bieber         AF6VN
        wlfraed@ix.netcom.com    HTTP://wlfraed.home.netcom.com/