12 Replies Latest reply: Jun 23, 2017 12:31 PM by Wai Meng Leong RSS

    Calculating  and grouping values from two identical column

    Wai Meng Leong

      Hi all,

       

      Below is a sample table of my data and I would like to an overall table where it calculates overall number of wins, overall number of draws and overall number of losses for each team (note that the list in HomeTeam and AwayTeam are the same)

       

       

      HomeTeamAwayTeamFTHGFTAGFTR
      BurnleySwansea01A
      Crystal PalaceWest Brom01A
      EvertonTottenham11D
      HullLeicester21H
      Man CitySunderland21H
      MiddlesbroughStoke11D
      SouthamptonWatford11D
      ArsenalLiverpool34A
      BournemouthMan United13A
      ChelseaWest Ham21H
      Man UnitedSouthampton20H
      BurnleyLiverpool20H
      LeicesterArsenal00D
      StokeMan City14A
      SwanseaHull02A
      TottenhamCrystal Palace10H
      WatfordChelsea12A
      West BromEverton12A
      SunderlandMiddlesbrough12A
      West HamBournemouth10H

       

      What i have done

      Picking the values separately from HomeTeam and AwayTeam is simple. What i am unable to do is to combine the records for HomeTeam and AwayTeam so that we have just 20 teams and the record will show for example:

       

      Team | Played | W | D | L |

      Burnley | 2 | 1 | 0 | 1 |

      Swansea | 2 | 0 | 0 | 2 |

      ....

       

      Any help is much appreciated!

       

      Regards,
      Wai Meng