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数据库中有两列，我想计算两个列中出现一个名称的次数。 COUNT函数本身对我不起作用，因为它只计算一列中的总数。
MySql Columns:
MySql列：
+-----------------+--------------+
| 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!
有任何想法吗？？谢谢！
2 个解决方案
#1
10
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:
您可以使用以下内容将使用UNION ALL将多列成员拆分为单个列。一旦它在单列中，那么您可以应用聚合函数计数：
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;
请参阅SQL Fiddle with Demo
#2
0
If a single name appears in 1 columns 2 times and more, above sql result will be wrong. Try it with "distinct" statement in subquery.
如果单个名称出现在1列2次以上，则上面的sql结果将是错误的。在子查询中使用“distinct”语句尝试它。
#1
10
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:
您可以使用以下内容将使用UNION ALL将多列成员拆分为单个列。一旦它在单列中，那么您可以应用聚合函数计数：
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;
请参阅SQL Fiddle with Demo
#2
0
If a single name appears in 1 columns 2 times and more, above sql result will be wrong. Try it with "distinct" statement in subquery.
如果单个名称出现在1列2次以上，则上面的sql结果将是错误的。在子查询中使用“distinct”语句尝试它。