MySQL: Count occurrences of distinct values in a column

What i want to do is :

example db

id         name
-----      ------
1          Mark
2          Mike
3          Paul
4          Mike
5          Mike
6          John
7          Mark

expected result

name       count
-----      -----
Mike       3
Mark       2
Paul       1
John       1

 

 

2 thoughts on “MySQL: Count occurrences of distinct values in a column

  • September 3, 2014 at 5:40 pm
    Permalink

    select user_id,
    sum(case when product_id = 1 then 1 else 0 end) as prod_1_count,
    sum(case when product_id = 2 then 1 else 0 end) as prod_2_count,
    sum(case when product_id = 3 then 1 else 0 end) as prod_3_count,
    sum(case when product_id = 4 then 1 else 0 end) as prod_4_count
    from your_table
    group by user_id

  • September 3, 2014 at 5:40 pm
    Permalink

    select name, count(*) as num
    from your_table
    group by name
    order by count(*) desc

Leave a Reply