CODEWITHSUNDEEP
mysql
PHPhil
PHPhil

Reputation: 1540

Count occurrences of distinct values multiple columns

I am trying to find a MySQL query that will find distinct values in a particular fields, count the number of occurrences of that value and then order the results.

I know how to do this with one row (just name1):

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

Is this possible for 2 or more rows or do I need to the previouse quere multiple times?

example:

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

result:

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

Upvotes: 1

Views: 858

Answers (1)

juergen d
juergen d

Reputation: 204766

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

Upvotes: 3

Related Questions