Group By Year, Name, and Count from Two Fields on Same Row

[akphidelt2007]

Alright, I've been doing this for too long to not understand what is going wrong here. This is pretty straightforward. I have one table with the columns...

 

ID | Year | Away_Team | Home_Team

-1---2010-----tacoma--------puyallup

-2---2010-----puyallup-------renton

-3---2010-----renton---------tacoma

-4---2010-----puyallup-------tacoma

 

What I want is just a simple count of the total # of games played by year and team regardless of it being an away or home game.

 

Year | Team | Games

-2010--tacoma---3

-2010--renton----2

 

Any help will be appreciated. Thanks

[Barand]

You need to use a UNION

SELECT Year
, Team
, COUNT(*) as Played
FROM (
    SELECT Year, Away_Team as Team
    FROM table
    UNION
    SELECT Year, Home_Team as Team
    FROM table
    ) teams
GROUP BY Year, Team

Edited March 21, 2016 by Barand