SQL count how many times a value appears in multiple columns?
mysql, sql
Solution
You can use the following which will unpivot your multiple columns of members into a single column using a `UNION ALL`. Once it is in the single column, then you can apply the aggregate function `count`:
select member, count(*) as total
from
(
select member1 as member
from yt
union all
select member2 as member
from yt
) d
group by member
order by total desc;
See SQL Fiddle with Demo
Problem
I have two columns in a mysql database that i would like to count how many times a single name appears in both columns. The COUNT function by itself doesn't work for me as it only counts the total in one column. MySql Columns: ``` +-----------------+--------------+ | Member1 | Member2 | +-----------------+--------------+ | John | Bill | | Bill | John | | Joe | John | | Bill | Joe | | John | Steve | +-----------------+--------------+ ``` Desired output: ``` +-----------------+--------------+ | Member | Total | +-----------------+--------------+ | John | 4 | | Bill | 3 | | Joe | 2 | | Steve | 1 | +-----------------+--------------+ ``` Any ideas?? Thanks!