Jump to content

Newbeeeee

Members
  • Posts

    16
  • Joined

  • Last visited

Profile Information

  • Gender
    Not Telling

Newbeeeee's Achievements

Member

Member (2/5)

0

Reputation

  1. 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.
  2. 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);
  3. Hey thank you for this, but I have a table tennis datasheet and each set is seperated in to sh1, sa1, sh2, sa2...
  4. 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)
  5. 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)
  6. 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.
×
×
  • 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.