Geek asks and answers

Does 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 alone does not work for me, since it only considers the quantity in one column.

MySql speakers:

+-----------------+--------------+
| Member1         | Member2      |
+-----------------+--------------+
| John            | Bill         | 
| Bill            | John         |
| Joe             | John         |
| Bill            | Joe          |
| John            | Steve        |
+-----------------+--------------+

Required Conclusion:

+-----------------+--------------+
| Member          |     Total    |
+-----------------+--------------+
| John            | 4            | 
| Bill            | 3            |
| Joe             | 2            |
| Steve           | 1            |
+-----------------+--------------+

Any ideas? Thank!

+5
source share
2 answers

You can use the following, which will reject multiple columns of members into a single column using UNION ALL. Once it is in one column, you can apply the aggregation function count:

select member, count(*) Total
from 
(
  select member1 as member
  from yt
  union all
  select member2
  from yt
) d
group by member
order by total desc;

See SQL Fiddle with Demo

+10
source

1 2 , sql . "" .

0

More articles:


All Articles