Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2013
    Posts
    3

    Head To Head query

    I am looking to design a database whereby I will be inputing historical data of soccer games from which I would like to derive Head to Head reports over a period of time. Example of data is as follows:

    January Team A: 3 Team B: 2

    February Team B: 2 Team A: 2

    I would have multiple teams ( more than 2 in the example above)

    I would like my Head To Head query to return:

    Team A vs Team B : 1 Win 1 Draw Goals Scored: 5 Goals Against: 4
    Team B vs Team A: 1 Loss 1 Draw Goals scored: 4 Goals Against: 5

    I tried creating a simple table as follows:

    MatchId, Date, TeamA, TeamAScore, TeamB, TeamBScore

    but getting stuck in attempting to aggregate the wins/losses/draws as per my example above.

    Any assistance would be b greatly appreciated.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    You'll probably need to create a UNION query like this, so you can query a complete list of teams and all their opponents and results:

    Code:
    select	TeamA as TeamName,
    	TeamAScore as TeamScore,
    	TeamB as Opponent,
    	TeamBScore as OpponentScore
    from	Matches
    union all
    select	TeamB as TeamName,
    	TeamBScore as TeamScore,
    	TeamA as Opponent,
    	TeamAScore as OpponentScore
    from	Matches
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Dec 2013
    Posts
    3
    Thank You. I will try this out shortly. Much appreciated

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another idea.

    Mimer SQL Developers - Mimer SQL-2003 Validator

    Mimer SQL-2003 Validator

    Check your SQL against the SQL-2003 standard using the Mimer SQL Validator 2003.

    The SQL-2003 Validator!

    Enter your SQL statement(s) in the box below and simply click the "Test SQL" button:

    Code:
    SELECT CASE t_o
           WHEN 1 THEN TeamA
           WHEN 2 THEN TeamB
           END  AS Team_Name
         , CASE t_o
           WHEN 1 THEN TeamA_Score
           WHEN 2 THEN TeamB_Score
           END  AS Team_Score
         , CASE t_o
           WHEN 1 THEN TeamB
           WHEN 2 THEN TeamA
           END  AS Opponent
         , CASE t_o
           WHEN 1 THEN TeamB_Score
           WHEN 2 THEN TeamA_Score
           END  AS Opponent_Score
     FROM  Matches
         , (VALUES 1 , 2 ) AS f(t_o)
    ;

    Code:
    Result:
    
    The following features outside Core SQL-2003 are used:
    
    F641, "Row and table constructors"
    F591, "Derived tables"
    F661, "Simple tables"

  5. #5
    Join Date
    Dec 2013
    Posts
    3
    Thanks - I've never used SELECT CASE to construct any query - bit confused looking at the statement you have provided as to what it's supposed to be doing. Will give it a try this evening and get back to you with my feedback. Thank you once again for your help

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The expected results of my query was same as the results expected by blindman.
    Quote Originally Posted by blindman View Post
    ..., so you can query a complete list of teams and all their opponents and results:

    ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •