bravo14 Posted March 10, 2009 Share Posted March 10, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/148728-solved-goalscorers-from-one-table-and-one-query/ Share on other sites More sharing options...
aschk Posted March 10, 2009 Share Posted March 10, 2009 Sorry, i'm not 100% sure of what it is you're asking for here. You have a table with data records in it, what are you trying to get out of it? Quote Link to comment https://forums.phpfreaks.com/topic/148728-solved-goalscorers-from-one-table-and-one-query/#findComment-780992 Share on other sites More sharing options...
bravo14 Posted March 10, 2009 Author Share Posted March 10, 2009 I am trying to show who has scored and how many in the differerent team_id and league fields to highlight the top scorers Quote Link to comment https://forums.phpfreaks.com/topic/148728-solved-goalscorers-from-one-table-and-one-query/#findComment-781325 Share on other sites More sharing options...
bravo14 Posted March 10, 2009 Author Share Posted March 10, 2009 The table result I am after is at http://www.yardleyhc.co.uk/goalscorers.htm Quote Link to comment https://forums.phpfreaks.com/topic/148728-solved-goalscorers-from-one-table-and-one-query/#findComment-781367 Share on other sites More sharing options...
fenway Posted March 13, 2009 Share Posted March 13, 2009 The table result I am after is at http://www.yardleyhc.co.uk/goalscorers.htm I don't click on links... some sample output, maybe? Quote Link to comment https://forums.phpfreaks.com/topic/148728-solved-goalscorers-from-one-table-and-one-query/#findComment-783839 Share on other sites More sharing options...
bravo14 Posted March 13, 2009 Author Share Posted March 13, 2009 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] Quote Link to comment https://forums.phpfreaks.com/topic/148728-solved-goalscorers-from-one-table-and-one-query/#findComment-784160 Share on other sites More sharing options...
fenway Posted March 15, 2009 Share Posted March 15, 2009 I'm sorry I don't follow... you need to count how many goals each person scored on each team? Quote Link to comment https://forums.phpfreaks.com/topic/148728-solved-goalscorers-from-one-table-and-one-query/#findComment-785162 Share on other sites More sharing options...
bravo14 Posted March 15, 2009 Author Share Posted March 15, 2009 That is it, all iof the scorers for each team is in one table, but we want to total how many each person has scored for each team Quote Link to comment https://forums.phpfreaks.com/topic/148728-solved-goalscorers-from-one-table-and-one-query/#findComment-785380 Share on other sites More sharing options...
fenway Posted March 16, 2009 Share Posted March 16, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/148728-solved-goalscorers-from-one-table-and-one-query/#findComment-785632 Share on other sites More sharing options...
bravo14 Posted March 16, 2009 Author Share Posted March 16, 2009 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? Quote Link to comment https://forums.phpfreaks.com/topic/148728-solved-goalscorers-from-one-table-and-one-query/#findComment-786195 Share on other sites More sharing options...
fenway Posted March 17, 2009 Share Posted March 17, 2009 You were on the right track... just remove the trailing comma after badgers before FROM. Quote Link to comment https://forums.phpfreaks.com/topic/148728-solved-goalscorers-from-one-table-and-one-query/#findComment-786661 Share on other sites More sharing options...
bravo14 Posted March 17, 2009 Author Share Posted March 17, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/148728-solved-goalscorers-from-one-table-and-one-query/#findComment-787123 Share on other sites More sharing options...
fenway Posted March 19, 2009 Share Posted March 19, 2009 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; Quote Link to comment https://forums.phpfreaks.com/topic/148728-solved-goalscorers-from-one-table-and-one-query/#findComment-788263 Share on other sites More sharing options...
bravo14 Posted March 19, 2009 Author Share Posted March 19, 2009 Thank you, just what I was after Quote Link to comment https://forums.phpfreaks.com/topic/148728-solved-goalscorers-from-one-table-and-one-query/#findComment-788268 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.