> TEAM
> name
> nickname
> win
> loss
> rating
> rank
>
> GAMES
> date
> away_team (fk)
> home_team (fk)
> away_score
> home_score
Shooting from the hip, with no actual data to experiment with,
and not knowing your back-end SQL server, the SQL could look
something like
SELECT
Team.*,
Coalesce(home.wins, 0) AS Home_Wins,
Coalesce(away.wins, 0) AS Away_Wins,
Coalesce(home.wins, 0) +
Coalesce(away.wins, 0) AS Total_Wins
Coalesce(home.played, 0) AS Home_Played,
Coalesce(away.played, 0) AS Away_Played,
Coalesce(home.played, 0) +
Coalesce(away.played, 0) AS Total_Played
FROM Team
LEFT OUTER JOIN (
SELECT
home_team AS Team,
Sum(
-- NB: not sure non-GROUP-BY values
-- can be referenced here.
CASE WHEN home_score > away_score
THEN 1
ELSE 0
END) AS wins,
Count(*) AS played
FROM Games
GROUP BY home_team
) Home
ON Home.team = Team.id
LEFT OUTER JOIN (
SELECT
away_team AS team,
Sum(
CASE WHEN home_score < away_score
THEN 1
ELSE 0
END) AS wins,
Count(*) AS played
FROM Games
GROUP BY home_team
) Away
ON Away.team = Team.id
Alternatively, it could look like
SELECT
Team.*,
(
SELECT Count(*)
FROM Games
WHERE (
home_team = Team.id
AND home_score > away_score
) OR (
away_team = Team.id
AND home_score < away_score
)
) / (
SELECT Count(*)
FROM Games
WHERE away_team = Team.id
OR home_team = Team.id
) AS Score
FROM Team
WHERE EXISTS (
SELECT 0
FROM Games
WHERE away_team = Team.id
OR home_team = Team.id
)
which would just give you the raw score (you may need to
Convert()/Cast() there bits into non-integer format for a
meaningful number) however, it only returns Teams that have
played games.
Hope this gives you some leads,
-tim
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
