Display league table

[isedeasy]

Hi

 

I have a table which holds all the results for my football teams league, the structure looks like:-

 

CREATE TABLE `league` (
`league_date` int(11) NOT NULL,
`league_comp` int(3) NOT NULL,
`league_season` int(2) NOT NULL,
`league_team` int(4) NOT NULL,
`league_points` int(1) NOT NULL,
`league_goals_for` int(2) NOT NULL,
`league_goals_against` int(2) NOT NULL,
`league_home` int(1) NOT NULL,
`league_opponent` int(4) NOT NULL,
PRIMARY KEY (`league_season`,`league_team`,`league_home`,`league_opponent`)
) ENGINE=InnoDB

 

the information I need to get is as follows:-

 

team name

games played

wins

losses

draws

points

goal difference

 

(the info with a strike-through is not the issue)

 

my query at the moment looks like this

 

$DB->query("SELECT l.*, o.opponent_name, COALESCE(SUM(league_points),0) AS points, COALESCE((SUM(league_goals_for)-SUM(league_goals_against)),0) AS goaldifference
           FROM league l
           LEFT JOIN opponents o ON o.opponent_id = l.league_team
           WHERE l.league_season = {$Config['season']['current']}
           GROUP BY l.league_team
           ORDER BY points DESC");

 

I am having a problem getting the the amount of games played and the amount of each kind of result. the information is in the table because of the `league_points` column (3=win,1=draw,0=loss).

 

Any help in finishing this query would be great, I know I could add extra columns but there must be a way of doing it with the info in the db. It's probably real simple lol

 

 

 

 

[isedeasy]

Wow I feel dumb, never knew MySQL had an IF function

 

Working query below

 

SELECT
            o.opponent_name,
            COALESCE(SUM(league_points),0) AS `points`,
            COALESCE((SUM(league_goals_for)-SUM(league_goals_against)),0) AS `goaldifference`,
            SUM(IF(l.league_points = 3, 1, 0)) AS `wins`,
            SUM(IF(l.league_points = 1, 1, 0)) AS `losses`,
            SUM(IF(l.league_points = 0, 1, 0)) AS `draws`,
            COUNT(l.league_points) AS `played`
            FROM league l
            LEFT JOIN opponents o ON o.opponent_id = l.league_team
            WHERE l.league_season = {$Config['season']['current']}
            GROUP BY l.league_team
            ORDER BY points DESC