Search code examples
mysqlsql

SQL count how many times a value appears in multiple columns?


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!


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