Jump to content
Newbeeeee

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

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
Posted (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 by Newbeeeee

Share this post


Link to post
Share on other sites
Posted (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 by Newbeeeee

Share this post


Link to post
Share on other sites
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 |
+---------+---------+--------+----------+

 

Share this post


Link to post
Share on other sites

Hey thank you for this, but I have a table tennis datasheet and each set is seperated in to sh1, sa1, sh2, sa2...

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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);

 

Share this post


Link to post
Share on other sites

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

  • Haha 1

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

  • Haha 1

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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. 

Share this post


Link to post
Share on other sites
44 minutes ago, pmichaels said:

Apparently NOT !, Still here !

What good will alternate accounts do for you, if nobody cares to answer your questions?

Share this post


Link to post
Share on other sites

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.