Help with SELECT statement

Question

First, I know that's a terribly vague title -- my knowledge of SQL is so weak that I don't really know the particulars of what I'm asking, so please bear with me.

I have a table that looks like this:

+--------+-----------+----------+--------+--------+
| gameid | team1     | team2    | score1 | score2 |
+--------+-----------+----------+--------+--------+
|      1 | New York  | Boston   |      1 |      2 |
|      2 | New York  | Boston   |      2 |      0 |
|      3 | Baltimore | New York |      3 |      1 |
|      4 | Boston    | New York |      3 |      0 |
+--------+-----------+----------+--------+--------+

Where gameid is a unique identifier, score1 corresponds to the score for team1, etc.

What I need is a query that will produce a summation of wins and losses for a given team against all opponents. Using New York for example, the query would create the following table:

+-----------+------+--------+
| team      | wins | losses |
+-----------+------+--------+
| Baltimore |    0 |      1 |
| Boston    |    1 |      2 |
+-----------+------+--------+

Answer 1

I don't have access to a SQL box right now (so this may be syntax-weak) and I'm not a huge fan of the db structure but this ought to get you going:

SELECT team, SUM(win) AS wins, SUM(loss) AS losses
FROM
 (SELECT team1 AS team, 
   CASE WHEN score1>score2 THEN 1 END AS win, 
   CASE WHEN score2>score1 THEN 1 END AS loss
  FROM YourTable
  UNION ALL
  SELECT team2 AS team, 
   CASE WHEN score2>score1 THEN 1 END AS win, 
   CASE WHEN score1>score2 THEN 1 END AS loss
  FROM YourTable)
GROUP BY team

Also, this ignores ties.

Note: I made this CW--please update if you can improve it

Answer 2

This is possible but the query is going to come out to be very complicated. If possible, perhaps you could reorganize your schema to encode some more information into it. For instance, rather than team1 and team2, you could have winning_team and losing_team; similiarly, using winning_score and losing_score you wouldn't have to check which score corresponded with which team. Then you could do a GROUP BY on losing_team to get all the wins for a particular team against each other team, and one on winning_team to get the losses (and avoid using score at all in this particular query).

Answer 3

ditto to @David's comments, This would be mist easier if your table structure was more normailized, but, with the schema as it is, Try this SQL

  Select T.Team,
     Sum(Case When S1.Score1 > S1.Score2 Or 
                   S2.Score2 > S2.Score1 Then 1 Else 0 End) Wins,
     Sum(Case When S1.Score1 < S1.Score2 Or 
                   S2.Score2 < S2.Score1 Then 1 Else 0 End Losses,
     Sum(Case When S1.Score1 = S1.Score2 Or 
                   S2.Score2 = S2.Score1 Then 1 Else 0 End Ties     
   (Select Distinct team1 team from Table
    Union Select Distinct team2 team from Table) T
      Left Join Table S1
         On S1.Team1 = T.Team
      Left Join Table S2
         On S2.Team2 = T.Team
  Group By T.Team

Answer 4

try this..

SELECT 
     team
    ,sum(wins) as wins
    ,sum(losses) as losses
FROM
    (
    SELECT 
         team1 as team
        ,SUM(CASE WHEN score1 > score2 THEN 1 ELSE 0 END) as wins
        ,SUM(CASE WHEN score1 < score2 THEN 1 ELSE 0 END) as losses
    FROM
        myTable
    GROUP BY
        team1
    UNION ALL
    SELECT 
         team2 as team
        ,SUM(CASE WHEN score2 > score1 THEN 1 ELSE 0 END) as wins
        ,SUM(CASE WHEN score2 < score1 THEN 1 ELSE 0 END) as losses
    FROM
        myTable
    GROUP BY
        team2 
    ) a
GROUP BY
team