Count number of times value appears in particular column in MySQL

Question

This has probably been asked before  but I m unable to make my way through the myriad of search results   Given a non-normalized MySQL table  what is the most optimized query to count the number of times each distinct value of column x was used   e g  Given a table containing  mike mary mike   Return results like   mike 2 mary 1   From the MySQL documentation  it would seem that count is an aggregate function that can be used with GROUP BY  but it s not doing what I want  it s returning the total number of rows in the GROUP BY  not the number of appearances for each row  i e  this does not work SELECT count email  as c FROM orders GROUP BY email

User · Answer

Take a look at the Group by function   What the group by function does is pretuty much grouping the similar value for a given field  You can then show the number of number of time that this value was groupped using the COUNT function   MySQL Documentation  You can also use the group by function with a good number of other function define by MySQL  see the above link    mysql gt  SELECT student name  AVG test score      - gt         FROM student     - gt         GROUP BY student name

User · Answer

select name  count    from table group by name    i think should do it

User · Answer

select email  count    as c FROM orders GROUP BY email

User · Answer

SELECT column name  COUNT column name  FROM table name GROUP BY column name

[mysql] Count number of times value appears in particular column in MySQL

Examples related to mysql

Examples related to aggregate-functions