Goalscorers fromone table and one query

[bravo14]

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

 

 

Name1st Lg1st FrFathomBadgersTotal

 

The data is stored in one table, the sql dump is below

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

[samshel]

try posting it in mysql forum, u will get better response this is PHP forum..