Sports result Query

[bravo14]

Hi guys

 

I am trying to create a query that displays the standard league table style results from a mysql table

 

the query I have got is

 

SELECT Count (match_id) AS played, Sum(yardley_goals) AS for, Sum(opposition_goals) AS against, Sum(yardley_goals-opposition_goals) AS gd, Sum(points) AS pts
FROM tbl_fixtures
WHERE yardley_goals <>Null;

 

 

I am also not sure how to show how may games have bene won, drawn and lost, let me know if you need to see the table structure

 

The columns I want are

 

 

Played Won Drawn Lost For Against Goal Difference Points

 

Any help would be great

[fenway]

How do you store that info?

[bravo14]

The table structure is here:

 

CREATE TABLE IF NOT EXISTS `tbl_fixtures` (

  `match_id` tinyint(4) NOT NULL auto_increment,

  `Opposition` varchar(30) NOT NULL,

  `date_id` int(11) NOT NULL,

  `HomeAway` varchar(4) NOT NULL,

  `Result` varchar(4) default NULL,

  `yardley_goals` tinyint(2) default NULL,

  `opposition_goals` tinyint(2) default NULL,

  `team_id` int(11) NOT NULL,

  `league` varchar(1) NOT NULL,

  `points` int(11) NOT NULL,

  PRIMARY KEY  (`match_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=81 ;

 

I can supply somke data if needed

[bravo14]

I have got the query sorted except for won, lost and drawn

 

SELECT Count(match_id) AS played, Sum(yardley_goals) AS goalsfor,
Sum(opposition_goals) AS against, Sum(yardley_goals-opposition_goals) AS gd,
Sum(points) AS pts
FROM tbl_fixtures
WHERE yardley_goals is not Null;

 

The field is stored in one field called result

 

Will I still be able to do this in one query or will it have to go into separate

[fenway]

You still haven't told us anything helpful -- what's in result?

[bravo14]

The values available for the result field are Won, Drawn Lost

 

When the scores are added, the user adds the opposition, result (Won, Drawn, Lost) Goals for and goals against)

[fenway]

I was confused because it wasn't an ENUM.

 

You can use "SUM(IF(result='won',1,0) AS won", etc.