How to get results of two players stats (home and away)

Newbeeeee · 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.

Barand · May 11, 2020

There's an example similar to that in the SQL tutorials in my sig

Newbeeeee · May 12, 2020

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

Newbeeeee · May 12, 2020

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

Barand · 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 |
+---------+---------+--------+----------+

Newbeeeee · 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...

Barand · 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.

Newbeeeee · 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);

gizmola · May 12, 2020

Hurray for repeating groups and rampant denormalization. Maybe just use a flat file or a spreadsheet?

Barand · May 12, 2020

Not to mention the use of TEXT type for names (65,000 character team names! - must be Welsh), dates and scores

gizmola · May 12, 2020

Apparently text is also a good way to store the eventtime too. I didn't notice that. 🐳

Newbeeeee · 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.

Barand · 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.

pmichaels · May 18, 2020

Apparently NOT !, Still here !

gizmola · 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.

gizmola · 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?

Sign In

How to get results of two players stats (home and away)

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information