Count number of times value appears in particular column in MySQL

mysql aggregate-functions
Mahmoud Al-Qudsi picture Mahmoud Al-Qudsi · Apr 18, 2012 · Viewed 148.8k times · Source

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

Nesim Razon picture Nesim Razon · Apr 18, 2012 
select email, count(*) as c FROM orders GROUP BY email

Related questions

SQL select only rows with max value on a column

I have this table for documents (simplified version here): +------+-------+--------------------------------------+ | id | rev | content | +------+-------+--------------------------------------+ | 1 | 1 | ... | | 2 | 1 | ... | | 1 | 2 | ... | | 1 | 3 | ... | +------+-------+--------------------------------------+ How do I select one row per id and only the greatest rev? With the above data, the result …

Read More
SELECTING with multiple WHERE conditions on same column

Ok, I think I might be overlooking something obvious/simple here... but I need to write a query that returns only records that match multiple criteria on the same column... My table is a very simple linking setup for applying …

Read More
How to SUM and SUBTRACT using SQL?

I am using MySQL and I have two tables: master_table ORDERNO ITEM QTY stock_bal ITEM BAL_QTY Master table has duplicate ORDERNO and ITEM values. I have get total QTY using SQL 'GROUP BY' clause. I need to …

Read More