# Thread: forumulae calculation in mysql

1. Registered User
Join Date
Feb 2004
Posts
7

## Unanswered: forumulae calculation in mysql

is it possible to have a colum that calculates something?

what i'm wanting to do is calculate if the team has won drawn or lost. I know how do do this in excel,

if team A is at home then
{If homescore > awayscor = W
if awayscore < homescore = L
else D
}
if team A is away then....

Home Team Score Away Team Score Outcome
Team 1 2 Team 12 1 ?
Team 3 1 Team 5 1 ?

etc...

hope anyone can help

jamie

2. Registered User
Join Date
Feb 2004
Location
Erehwon
Posts
18

## Re: forumulae calculation in mysql

You need a CASE statement.
http://www.mysql.com/doc/en/CASE_Statement.html

[code]
select home_team, home_score, away_team, away_score,
case
when home_score>away_score then 'W'
when home_score<away_score then 'L'
else 'D'
end as outcome
from ......
[\code]

Originally posted by jimmy2bob
is it possible to have a colum that calculates something?

what i'm wanting to do is calculate if the team has won drawn or lost. I know how do do this in excel,

if team A is at home then
{If homescore > awayscor = W
if awayscore < homescore = L
else D
}
if team A is away then....

Home Team Score Away Team Score Outcome
Team 1 2 Team 12 1 ?
Team 3 1 Team 5 1 ?

etc...

hope anyone can help

jamie

3. Registered User
Join Date
Feb 2004
Posts
7

## Re: forumulae calculation in mysql

4. Registered User
Join Date
Feb 2004
Location
Erehwon
Posts
18
the case statment goes into the Select statement you're using to retrieve your records

5. Registered User
Join Date
Apr 2003
Location
Edison, NJ / Oakland, NJ (Work)
Posts
32
Wouldn't you want to have a W/L/D listed for each team. The win/loss/draw isn't assigned to the game, it's assigned to the teams who played.

Team 1: (W) 13 - Team 23: (L) 8

something like that huh?

6. Registered User
Join Date
Feb 2004
Location
Erehwon
Posts
18
Good point. Tracking W/L/D at a team level would certainly make calculating team standings easier. Maybe three tables then:

Team
-team_id (primary)
-team_name

Game
-game_id (primary)
-home_team
-away_team

Score
-team_id (foreign)
-game_id (foreign)
-score
-outcome

7. Registered User
Join Date
Feb 2004
Posts
7
i dont need the table calculating as i've already done that.. and calculated the home and away tables..

What i'm wanting is the form for the last 6 games a team has played.

I have selected the games, limited to the last six and now I want to know if the team has w, d or l a game.

It will eventually look like;

Form (Last 6) WWWDDL.

hope this makes sense...

8. Registered User
Join Date
Feb 2004
Posts
7
its alright, fixed it!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•