Skip to content
Advertisement

Count from multiple columns and rows simultaneously in SQL

I have a table of matches which look like:

Match Win Lost
MOne   T1  T2
MTwo   T2  T3
MThr   T1  T3
MFou   T3  T4

I need to find the total matches played by every team and the output should be like this:

Team  Total_matches
T1      2
T2      2
T3      3
T4      1

I know about SUM() and GROUP BY clauses but not able to do it on multiple columns.
Any help would be highly appreciated.

Advertisement

Answer

You can use union all to unpivot the data and then aggregate:

select team, count(*)
from ((select win as team
       from matches
      ) union all
      (select lost as team
       from matches
      )
     ) m
group by team
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement