How to use SUM from MySQL on several occasions with the same column

Hi there,

I am trying to create a page that once I have entered football results into the database, it will dynamically produce the table showing games played, won, drawn, lost, goals for, goals against and goal difference. The two tables are:

*efl_teams
team_id
team_name

*efl_matches
efl_match_id
efl_match_home (team_id of home_team)
efl_match_home_score
efl_match_away (team_id of away team)
efl_match_away_score
efl_match_played (0 if match not played, 1 if match played)

I have managed to put together some code that will show in the descending order all the teams that have won matches. I cannot figure out how to use the same to list teams that have drawn or lost matches though:

mySQLwins="SELECT *, SUM(efl_match_played) as wins FROM efl_teams, efl_matches WHERE team_id = efl_match_home AND efl_match_home_score > efl_match_away_score OR team_id = efl_match_away AND efl_match_home_score < efl_match_away_score GROUP BY team_id ORDER BY wins DESC"

Any help would be great!

el_geezer,

Are you trying to have all the SUMS in one single query, or are you trying to do this with multiple queries?

What server-side language are you using to integrate this?

Hi,

I'm using ASP and the aim is to use all the SUMs in one single query then loop through all the teams with the most points in descending order. I'd be interested to know if the loop could be done with multiple queries though.

tom