Jump to content

Sports result Query


bravo14
 Share

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.