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

 

 

Related Posts

  • 32
    Here is an example to select multiple column of a table from select statement in asterisk. exten => h,1,MYSQL(Connect conn localhost username password database) exten => h,n,MYSQL(Query res ${conn} 'SELECT call_id,callerid,calltime FROM todaycall WHERE calltime=curdate() order by calltime desc limit 1') exten => h,n,MYSQL(Fetch fid ${r} call_id callerid calltime) exten…
    Tags: column, example, mysql
  • 31
    What is database? Database is an organized collection of information about an entity having controlled redundancy and serves multiple applications. DBMS (database management system) is an application software that is developed to create and manipulate the data in database. A query language can easily access a data in a database.…
    Tags: mysql, db

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

    Reply
  • September 3, 2014 at 5:40 pm
    Permalink

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

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: