Newbeeeee Posted May 11, 2020 Share Posted May 11, 2020 I will have a table of data with player names and their total scores. (home and away matches) I will have two drop down boxes where I could select two players to show the stats of. I have the following database structure. date, player_home, player_away, home_score, away_score each player can play both home and away. What I can not get my head around is how to show the results of just two players with all results home and away. Quote Link to comment https://forums.phpfreaks.com/topic/310750-how-to-get-results-of-two-players-stats-home-and-away/ Share on other sites More sharing options...
Barand Posted May 11, 2020 Share Posted May 11, 2020 There's an example similar to that in the SQL tutorials in my sig Quote Link to comment https://forums.phpfreaks.com/topic/310750-how-to-get-results-of-two-players-stats-home-and-away/#findComment-1577870 Share on other sites More sharing options...
Newbeeeee Posted May 12, 2020 Author Share Posted May 12, 2020 (edited) I have found a query that I altered to suit my own field names and it seem to show almost what I was after. The main thing I wanted was to get a grand total of points each play has had with the other player they played with. this is the query. SELECT DISTINCT name_home, name_away, CONCAT(IF(name_home = LEAST(name_home, name_away),team1wins, team2wins),' - ', IF(name_away = LEAST(name_home, name_away),team1wins, team2wins)) AS Head2HeadRecord, CONCAT(LEAST(name_home, name_away), GREATEST(name_home, name_away)) AS surrogateSort, notplayed FROM armenia JOIN (SELECT LEAST(name_home, name_away) AS team1, GREATEST(name_home, name_away) AS team2, SUM(IF(LEAST(name_home, name_away) = name_home AND final_home_score > final_away_score, 1, IF(LEAST(name_home, name_away) = name_away AND final_away_score > final_home_score,1,0) ) ) AS team1Wins, SUM(IF(GREATEST(name_home, name_away) = name_home AND final_home_score > final_away_score, 1, IF(GREATEST(name_home, name_away) = name_away AND final_away_score > final_home_score,1,0) ) ) AS team2Wins, SUM(IF(final_home_score=0 AND final_away_score=0,1,0)) AS notPlayed FROM armenia GROUP BY team1, team2) AS trecords ON LEAST(name_home, name_away) = team1 AND GREATEST(name_home, name_away) = team2 ORDER BY surrogateSort, name_home, name_away And it shows the following results. (home player v away player total home player wins - total away player wins) Player1 v Player2 25 - 20 Player2 v Player1 20 - 25 Player1 v Player3 7 - 1 Player3 v Player1 1 - 7 Player1 v Player4 19 - 7 Player4 v Player1 7 - 19 The results I would like to get are just one of each group... (so the left most player is in alphabetic order) with the total number of point that each player has got playing that player in brackets. Player1 v Player2 25 - 20 (120 - 101) Player1 v Player3 7 - 1 (102 - 10) Player1 v Player4 19 - 7 (103 - 99) Edited May 12, 2020 by Newbeeeee Quote Link to comment https://forums.phpfreaks.com/topic/310750-how-to-get-results-of-two-players-stats-home-and-away/#findComment-1577906 Share on other sites More sharing options...
Newbeeeee Posted May 12, 2020 Author Share Posted May 12, 2020 (edited) Sorry forgot to add in the new query and data sheets table layout (SetHome1, SetAway1 ...) date, player_home, player_away, home_score, away_score, sh1, sa1, sh2, sa2, sh3, sa3, sh4, sa4, sh5, sa5, sh6, sa6, sh7, sa7, sh8, sa8 SELECT DISTINCT name_home, name_away, CONCAT(IF(name_home = LEAST(name_home, name_away),team1wins, team2wins),' - ', IF(name_away = LEAST(name_home, name_away),team1wins, team2wins)) AS Head2HeadRecord, CONCAT(LEAST(name_home, name_away), GREATEST(name_home, name_away)) AS surrogateSort, notplayed FROM `table` JOIN (SELECT LEAST(name_home, name_away) AS team1, GREATEST(name_home, name_away) AS team2, SUM(IF(LEAST(name_home, name_away) = name_home AND final_home_score > final_away_score, 1, IF(LEAST(name_home, name_away) = name_away AND final_away_score > final_home_score,1,0) ) ) AS team1Wins, SUM(IF(GREATEST(name_home, name_away) = name_home AND final_home_score > final_away_score, 1, IF(GREATEST(name_home, name_away) = name_away AND final_away_score > final_home_score,1,0) ) ) AS team2Wins, SUM(IF(final_home_score=0 AND final_away_score=0,1,0)) AS notPlayed FROM `table` GROUP BY team1, team2) AS trecords ON LEAST(name_home, name_away) = team1 AND GREATEST(name_home, name_away) = team2 INNER JOIN (SELECT SUM(`sh1`) + SUM(`sh2`) + SUM(`sh3`) + SUM(`sh4`) + SUM(`sh5`) + SUM(`sh6`) + SUM(`sh7`) + SUM(`sh8`) AS home_total FROM `table` GROUP BY `sh1`, `sh2`, `sh3`, `sh4`, `sh5`, `sh6`, `sh7`, `sh8`) WHERE (name_home = 'Player1' AND name_away = 'Player2') OR (name_home = 'Player2' AND name_away = 'Player1') ORDER BY surrogateSort, name_home, name_away The WHERE clause is so I just get stats of just two players. The INNER JOIN is where I tried to get it to add the sets scores for each player. (I am looking to get all sets added together for each player both home and away) Edited May 12, 2020 by Newbeeeee Quote Link to comment https://forums.phpfreaks.com/topic/310750-how-to-get-results-of-two-players-stats-home-and-away/#findComment-1577910 Share on other sites More sharing options...
Barand Posted May 12, 2020 Share Posted May 12, 2020 fixture_view (input data) +---------+--------+----------+-----------+-----------+----------+ | idmatch | weekno | hometeam | homegoals | awaygoals | awayteam | +---------+--------+----------+-----------+-----------+----------+ | 4 | 1 | Laker | 1 | 1 | Jardine | | 1 | 1 | Cowdrey | 1 | 0 | Grace | | 2 | 2 | Grace | 2 | 2 | Cowdrey | | 6 | 2 | Jardine | 1 | 3 | Laker | | 9 | 3 | Laker | 2 | 4 | Cowdrey | | 3 | 3 | Jardine | 4 | 4 | Grace | | 10 | 4 | Cowdrey | 4 | 4 | Laker | | 5 | 4 | Grace | 1 | 2 | Jardine | | 7 | 5 | Cowdrey | 2 | 0 | Jardine | | 8 | 5 | Grace | 0 | 3 | Laker | | 12 | 6 | Jardine | 1 | 4 | Cowdrey | | 11 | 6 | Laker | 4 | 1 | Grace | +---------+--------+----------+-----------+-----------+----------+ SELECT team1 as team , team2 as versus , SUM(scored) as scored , SUM(conceded) as conceded FROM ( SELECT hometeam as team1 , awayteam as team2 , homegoals as scored , awaygoals as conceded FROM fixture_view f UNION ALL SELECT awayteam as team1 , hometeam as team2 , awaygoals as scored , homegoals as conceded FROM fixture_view f ) games GROUP BY team1, team2; +---------+---------+--------+----------+ | team | versus | scored | conceded | +---------+---------+--------+----------+ | Cowdrey | Grace | 3 | 2 | | Cowdrey | Jardine | 6 | 1 | | Cowdrey | Laker | 8 | 6 | | Grace | Cowdrey | 2 | 3 | | Grace | Jardine | 5 | 6 | | Grace | Laker | 1 | 7 | | Jardine | Cowdrey | 1 | 6 | | Jardine | Grace | 6 | 5 | | Jardine | Laker | 2 | 4 | | Laker | Cowdrey | 6 | 8 | | Laker | Grace | 7 | 1 | | Laker | Jardine | 4 | 2 | +---------+---------+--------+----------+ Quote Link to comment https://forums.phpfreaks.com/topic/310750-how-to-get-results-of-two-players-stats-home-and-away/#findComment-1577911 Share on other sites More sharing options...
Newbeeeee Posted May 12, 2020 Author Share Posted May 12, 2020 Hey thank you for this, but I have a table tennis datasheet and each set is seperated in to sh1, sa1, sh2, sa2... Quote Link to comment https://forums.phpfreaks.com/topic/310750-how-to-get-results-of-two-players-stats-home-and-away/#findComment-1577913 Share on other sites More sharing options...
Barand Posted May 12, 2020 Share Posted May 12, 2020 22 hours ago, Newbeeeee said: I have the following database structure. date, player_home, player_away, home_score, away_score not what you said before. 7 minutes ago, Newbeeeee said: I have a table tennis datasheet and each set is seperated in to sh1, sa1, sh2, sa2... and not being able to see your screen I haven't a clue what that looks like. Quote Link to comment https://forums.phpfreaks.com/topic/310750-how-to-get-results-of-two-players-stats-home-and-away/#findComment-1577914 Share on other sites More sharing options...
Newbeeeee Posted May 12, 2020 Author Share Posted May 12, 2020 CREATE TABLE `table` ( `id` bigint(20) NOT NULL, `eventtime` text NOT NULL, `name_home` text NOT NULL, `name_away` text NOT NULL, `final_home_score` text NOT NULL, `final_away_score` text NOT NULL, `sh1` int(11) NOT NULL, `sa1` int(11) NOT NULL, `sh2` int(11) NOT NULL, `sa2` int(11) NOT NULL, `sh3` int(11) NOT NULL, `sa3` int(11) NOT NULL, `sh4` int(11) NOT NULL, `sa4` int(11) NOT NULL, `sh5` int(11) NOT NULL, `sa5` int(11) NOT NULL, `sh6` int(11) NOT NULL, `sa6` int(11) NOT NULL, `sh7` int(11) NOT NULL, `sa7` int(11) NOT NULL, `sh8` int(11) NOT NULL, `sa8` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `table` (`id`, `eventtime`, `name_home`, `name_away`, `final_home_score`, `final_away_score`, `sh1`, `sa1`, `sh2`, `sa2`, `sh3`, `sa3`, `sh4`, `sa4`, `sh5`, `sa5`, `sh6`, `sa6`, `sh7`, `sa7`, `sh8`, `sa8`) VALUES (1, '11.05. 21:55', 'Player1', 'Player2', '3', '1', 11, 6, 10, 12, 11, 4, 11, 5, 0, 0, 0, 0, 0, 0, 0, 0), (2, '11.05. 21:10', 'Player2', 'Player1', '0', '3', 13, 15, 6, 11, 9, 11, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0); Quote Link to comment https://forums.phpfreaks.com/topic/310750-how-to-get-results-of-two-players-stats-home-and-away/#findComment-1577917 Share on other sites More sharing options...
gizmola Posted May 12, 2020 Share Posted May 12, 2020 Hurray for repeating groups and rampant denormalization. Maybe just use a flat file or a spreadsheet? Quote Link to comment https://forums.phpfreaks.com/topic/310750-how-to-get-results-of-two-players-stats-home-and-away/#findComment-1577918 Share on other sites More sharing options...
Barand Posted May 12, 2020 Share Posted May 12, 2020 Not to mention the use of TEXT type for names (65,000 character team names! - must be Welsh), dates and scores 1 Quote Link to comment https://forums.phpfreaks.com/topic/310750-how-to-get-results-of-two-players-stats-home-and-away/#findComment-1577921 Share on other sites More sharing options...
gizmola Posted May 12, 2020 Share Posted May 12, 2020 Apparently text is also a good way to store the eventtime too. I didn't notice that. 🐳 Quote Link to comment https://forums.phpfreaks.com/topic/310750-how-to-get-results-of-two-players-stats-home-and-away/#findComment-1577922 Share on other sites More sharing options...
Newbeeeee Posted May 16, 2020 Author Share Posted May 16, 2020 Apparently... this is a good place to get pointless remarks on a post, unless you are trying to make a point. Why not just SAY it ! Twat. 1 Quote Link to comment https://forums.phpfreaks.com/topic/310750-how-to-get-results-of-two-players-stats-home-and-away/#findComment-1578058 Share on other sites More sharing options...
Barand Posted May 16, 2020 Share Posted May 16, 2020 Whereas that may be a suitable format for a spreadsheet table it is not fit for purpose in a relational database. Further, your choices of column type are totally unsuitable for the data in them. But as you won't be here much longer, that's all you'll get. Quote Link to comment https://forums.phpfreaks.com/topic/310750-how-to-get-results-of-two-players-stats-home-and-away/#findComment-1578060 Share on other sites More sharing options...
pmichaels Posted May 18, 2020 Share Posted May 18, 2020 Apparently NOT !, Still here ! Quote Link to comment https://forums.phpfreaks.com/topic/310750-how-to-get-results-of-two-players-stats-home-and-away/#findComment-1578125 Share on other sites More sharing options...
gizmola Posted May 18, 2020 Share Posted May 18, 2020 On 5/16/2020 at 8:42 AM, Newbeeeee said: Apparently... this is a good place to get pointless remarks on a post, unless you are trying to make a point. Why not just SAY it ! Twat. The points are clear. You need to spend some time learning about mysql datatypes and relational design. Those are your issues, not ours. This is why the comments and links were provided. You also had one question, and then turned on a dime and turned it into an entirely different question, and then one post later, an entirely different question. I don't find that particularly respectful of the people you are seeking help from. Like your database structure, it's intellectually lazy. Quote Link to comment https://forums.phpfreaks.com/topic/310750-how-to-get-results-of-two-players-stats-home-and-away/#findComment-1578126 Share on other sites More sharing options...
gizmola Posted May 18, 2020 Share Posted May 18, 2020 44 minutes ago, pmichaels said: Apparently NOT !, Still here ! What good will alternate accounts do for you, if nobody cares to answer your questions? Quote Link to comment https://forums.phpfreaks.com/topic/310750-how-to-get-results-of-two-players-stats-home-and-away/#findComment-1578127 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.