Jump to content

[SOLVED] Goalscorers from one table and one query


bravo14

Recommended Posts

I am trying to recreate the table structure below using one query, not sure if it can be done

 

 

Name 1st Lg 1st Fr Fathom Badgers Total

The data is stored in one table, the sql dump is below
Code: [select]CREATE TABLE IF NOT EXISTS `tbl_scorers` (
  `scorer_id` int(11) NOT NULL auto_increment,
  `match_id` int(11) NOT NULL,
  `scorer` varchar(30) NOT NULL,
  `goals` int(11) NOT NULL,
  `team_id` int(11) NOT NULL,
  `league` varchar( NOT NULL,
  PRIMARY KEY  (`scorer_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table for goal scorers across the club' AUTO_INCREMENT=33 ;

--
-- Dumping data for table `tbl_scorers`
--

INSERT INTO `tbl_scorers` (`scorer_id`, `match_id`, `scorer`, `goals`, `team_id`, `league`) VALUES
(1, 16, 'Andy Pyke', 1, 1, 'league'),
(2, 14, 'Andy Elves', 1, 1, 'league'),
(3, 14, 'Rob McGuigan', 1, 1, 'league'),
(4, 14, 'Abrar Baig', 2, 1, 'league'),
(5, 14, 'Andy Pyke', 2, 1, 'league'),
(6, 25, 'Andy Pyke', 6, 2, 'friendly'),
(7, 25, 'Munib Altaf', 1, 2, 'friendly'),
(8, 26, 'Munib Altaf', 1, 2, 'friendly'),
(9, 26, 'Ismail Mutlib', 1, 2, 'friendly'),
(10, 26, 'Robin Stanley', 4, 2, 'friendly'),
(11, 28, 'Luke Menzil', 1, 2, 'friendly'),
(12, 28, 'Scott Oliver', 1, 2, 'friendly'),
(13, 28, 'Shoeb Siddiqui', 1, 2, 'friendly'),
(14, 28, 'Saleem Hussain', 1, 2, 'friendly'),
(15, 30, 'Jonathan Smith', 1, 2, 'friendly'),
(16, 30, 'Scott Oliver', 1, 2, 'friendly'),
(17, 30, 'Robin Stanley', 1, 2, 'friendly'),
(18, 30, 'Munib Altaf', 2, 2, 'friendly'),
(19, 32, 'Robin Stanley', 1, 2, 'friendly'),
(20, 32, 'Scott Oliver', 1, 2, 'friendly'),
(21, 32, 'Ismail Mutlib', 1, 2, 'friendly'),
(22, 32, 'Andy Pyke', 1, 2, 'friendly'),
(23, 34, 'Luke Menzil', 1, 2, 'friendly'),
(24, 34, 'Jordan Allen', 1, 2, 'friendly'),
(25, 34, 'Ismail Mutlib', 2, 2, 'friendly'),
(26, 34, 'Robin Stanley', 1, 2, 'friendly'),
(27, 36, 'Ismail Mutlib', 4, 2, 'friendly'),
(28, 36, 'Robin Stanley', 1, 2, 'friendly'),
(29, 36, 'Shoeb Siddiqui', 1, 2, 'friendly'),
(30, 36, 'Chris Wadhams', 1, 2, 'friendly'),
(31, 36, 'Glenn Sherrard', 1, 2, 'friendly'),
(32, 36, 'Gavin Hetherington', 1, 2, 'friendly'); 

I have tried the query below but not getting the desired result

SELECT
scorer,
Sum(t1.firstleague) AS firstleague,
Sum(t2.firstfriendly) AS firstfriendly,
Sum(t3.fathoms) AS fathoms,
Sum(t4.badgers) AS badgers,
Sum(goals) AS goals_scored
FROM tbl_scorers
LEFT JOIN (SELECT sum(goals), 1 AS firstleague FROM tbl_scorers WHERE team_id =1 and league="league")
AS t1
LEFT JOIN (SELECT goals, 1 AS firstfriendly FROM tbl_scorers WHERE team_id =1 and league="friendly")
AS t2
LEFT JOIN (SELECT goals, 1 AS fathoms FROM tbl_scorers WHERE team_id =2 and league="friendly")
AS t3
LEFT JOIN (SELECT goals, 1 AS badgers FROM tbl_scorers WHERE team_id =3 and league="friendly")
AS t4
group by scorer

Link to comment
Share on other sites

I have atatched an image

 

The table below will give a sort of idea

 

This table is currently manually updated but I want it to pull from the db.

 

Name 1st Lg 1st Fr Fathom Badgers Total

 

 

Andy Pyke  7 2  7      16 

Ismail Mutlib  1        11  4 16

Munib Altaf        7  5  12

Robin Stanley    11  11

Saleem Hussain    2  3  5

Abra Baig 3      3

Andy Elves  2  1            3   

Sameer Hussain            3  3 

Scott Oliver          3      3 

Shoeb Siddiqui      2  1  3

Haseeb Altaf          3 3

Rob McGuigan 1  2    3

Luke Menzil          2        2 

Ben Cross  1        1 

Naveed Akram              1  1 

Zach Jarvis              1  1 

Jordan Allen          1    1 

Jon Smith    1  1

Dave Donkin 1      1

Chris Wadhams    1    1

Glenn Sherard    1  1

Gavin Hetherington    1  1

Haider Iqbal        1 1

 

The MySQL table dictates the team (team_id and whether it is a league or a friendly game)

 

[attachment deleted by admin]

Link to comment
Share on other sites

Why not simply:

 

SELECT
scorer,
Sum(IF(team_id =1 and league="league",1,0) AS firstleague,
Sum(IF(team_id =1 and league="friendly",1,0) AS firstfriendly,
Sum(IF(team_id =2 and league="friendly",1,0) AS fathoms,
Sum(IF(team_id =3 and league="friendly",1,0) AS badgers,
FROM tbl_scorers
group by scorer

Link to comment
Share on other sites

I put the following query in and got an error of

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS firstleague,

Sum(IF(team_id =1 and league="friendly",1,0) AS firstfriendly,

' at line 3

 

SELECT
scorer,
Sum(IF(team_id =1 and league="league",1,0) AS firstleague,
Sum(IF(team_id =1 and league="friendly",1,0) AS firstfriendly,
Sum(IF(team_id =2 and league="friendly",1,0) AS fathoms,
Sum(IF(team_id =3 and league="friendly",1,0) AS badgers,
FROM tbl_scorers
group by scorer

so added some brackets in and got the folowing

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from `tbl_scorers`

group by scorer' at line 7 with the following query

 

SELECT
scorer,
Sum(IF(team_id =1 and league="league",1,0)) AS firstleague,
Sum(IF(team_id =1 and league="friendly",1,0)) AS firstfriendly,
Sum(IF(team_id =2 and league="friendly",1,0)) AS fathoms,
Sum(IF(team_id =3 and league="friendly",1,0)) AS badgers,
from `tbl_scorers`
group by scorer
;

 

any ideas?

Link to comment
Share on other sites

I am nearly there now

 

I now have the following query

SELECT
scorer,
sum(goals) as total_goals,
Sum(IF(team_id =1 and league="league",1,0)) AS firstleague,
Sum(IF(team_id =1 and league="friendly",1,0)) AS firstfriendly,
Sum(IF(team_id =2 and league="friendly",1,0)) AS fathoms,
Sum(IF(team_id =3 and league="friendly",1,0)) AS badgers
from `tbl_scorers`
group by scorer
order by total_goals desc
;

 

and get the following result

 

'Andy Pyke', 10, 2, 0, 2, 0

'Ismail Mutlib', 8, 0, 0, 4, 0

'Robin Stanley', 8, 0, 0, 5, 0

'Munib Altaf', 4, 0, 0, 3, 0

'Scott Oliver', 3, 0, 0, 3, 0

'Abrar Baig', 2, 1, 0, 0, 0

'Shoeb Siddiqui', 2, 0, 0, 2, 0

'Andy Elves', 2, 1, 0, 0, 0

'Luke Menzil', 2, 0, 0, 2, 0

'Saleem Hussain', 1, 0, 0, 1, 0

'Chris Wadhams', 1, 0, 0, 1, 0

'Rob McGuigan', 1, 1, 0, 0, 0

'Jonathan Smith', 1, 0, 0, 1, 0

'Glenn Sherrard', 1, 0, 0, 1, 0

'Jordan Allen', 1, 0, 0, 1, 0

'Gavin Hetherington', 1, 0, 0, 1, 0

 

What I need to do is total the goals field for each team like I have managed to do for the total goals, at the moment it is counting the number of records.

Link to comment
Share on other sites

Ha! Funny... sorry about that:

 

SELECT
scorer,
sum(goals) as total_goals,
Sum(IF(team_id =1 and league="league",goals,0)) AS firstleague,
Sum(IF(team_id =1 and league="friendly",goals,0)) AS firstfriendly,
Sum(IF(team_id =2 and league="friendly",goals,0)) AS fathoms,
Sum(IF(team_id =3 and league="friendly",goals,0)) AS badgers
from `tbl_scorers`
group by scorer
order by total_goals desc;

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

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.

×
×
  • 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.