计数多列不同值的出现次数

Question

我试图找到一个MySQL查询，该查询将在特定字段中查找不同的值，计数该值的出现次数，然后对结果进行排序。

我知道如何用一行(只有name1)来完成这个任务：

SELECT name1, COUNT( * ) AS count
FROM table
GROUP BY name1
ORDER BY `count` ASC

这是可能的两个或更多的行，或我需要到前面的quere多次？

示例：

    id         name1        name2
    -----      ------       ------
    1          Mark         Paul
    2          Mike         John
    3          Paul         Mike
    4          Cleo         Bert
    5          Mike         Cleo
    6          John         Mark
    7          Mark         Cleo

结果：

    names      count1       count2
    ------     -------      ------
    Bert       0            1
    Cleo       1            2
    john       1            1
    Mark       2            1
    Mike       2            1
    Paul       1            1

Answer 1

SELECT name1, COALESCE(sum(count1),0) as count1, COALESCE(sum(count2),0) as count2
FROM 
(
   select name1, count(*) as count1, null as count2 from your_table group by name1
   union all 
   select name2, null, count(*) from your_table group by name2
) tmp
GROUP BY name1
ORDER BY name1 ASC