Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #27897

Re: Computing win/loss records in Python

Show key headers only | View raw

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/

Back to comp.lang.python | Previous | Next | Find similar | Unroll thread

Thread

Re: Computing win/loss records in Python Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2012-08-25 23:13 -0400

csiph-web