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
You can use aggregate function count() with group by. The syntax is as follows. select yourColumnName,count(*) as anyVariableName from yourtableName group by yourColumnName; To understand the above syntax, let us create a table.
The MySQL COUNT() function allows you to count how many times a certain value appears in your MySQL database. The function can also help you to count how many rows you have in your MySQL table.
select email, count(*) as c FROM orders GROUP BY email
SELECT column_name, COUNT(column_name) FROM table_name GROUP BY column_name
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With