Summing over multiple columns

Question

Hi I have a table like this

Year Column1 column2 column3
 2015     A               B             C
 2015     D               A             B
 2016     F               C              A

And I need to be able to sum across the column how many times a certain subset shows up. What I want the summary table to look like is this

Year Letter count
2015  A        2
2016  A        1
2015  B        2

If anyone can help me with this I'd really appreciate it!

Answer 1

You can use union all to break the data apart, and then group by to bring it back together:

select year, letter, count(*) 
from ((select year, column1 as letter from t) union all
      (select year, column2 as letter from t) union all
      (select year, column3 as letter from t) 
     ) yc
group by year, letter;

For a set of particular letters, add a where letter in ('A', 'B') clause.