Join two tables 1

[djhawthorn]

I have the following query:

SELECT Team1, COUNT(Winner) AS Won FROM Games WHERE Winner = -1 GROUP BY Team1
UNION ALL
SELECT Team2, COUNT(Winner) AS Won FROM Games WHERE Winner = -1 GROUP BY Team2;

Which returns the following:

+-------+-----+
| Team1 | Won |
+-------+-----+
|     1 |   1 |
|     9 |   1 |
|     4 |   1 |
|     9 |   1 |
+-------+-----+

I need it to merge the Team1 column though to get the following output:

+-------+-----+
| Team1 | Won |
+-------+-----+
|     1 |   1 |
|     9 |   2 |
|     4 |   1 |
+-------+-----+

Can someone help?
Cheers.

The dumber they think you are, the more surprised they'll be when you kill them!

 

[r937]

SELECT Team1    AS Team
     , SUM(Won) AS Won
  FROM ( [i]put your UNION query here[/i] ) AS dt
GROUP
    BY Team1

r937.com | rudy.ca

 

[djhawthorn]

Sweet.

So now I need to merge that with another query:

SELECT Winner AS TeamWon, COUNT(Winner) AS GamesWon
FROM Games
WHERE Winner > 0
GROUP BY TeamWon

Which returns something like:

+---------+----------+
| TeamWon | GamesWon |
+---------+----------+
|       1 |        3 |
|       2 |        3 |
|       3 |        1 |
|       4 |        1 |
|       5 |        1 |
|       6 |        1 |
|       7 |        2 |
|       8 |        1 |
|       9 |        1 |
+---------+----------+

I've tried various combinations of UNION and nested SELECTS, but cant get it to work. I think I may need to JOIN them but cant seem to get the syntax right...

Thanks again

The dumber they think you are, the more surprised they'll be when you kill them!

 

[djhawthorn]

Nevermind - worked it out.

I had the LEFT JOIN around the wrong way - thanks for the help.

The dumber they think you are, the more surprised they'll be when you kill them!