Mahmoud Al-Qudsi Mahmoud Al-Qudsi - 3 months ago 14
MySQL Question

Count number of times value appears in particular column in MySQL

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

Answer
select email, count(*) as c FROM orders GROUP BY email
Comments