Count number of times value appears in particular column in MySQL
MysqlAggregate FunctionsMysql Problem Overview
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
Mysql Solutions
Solution 1 - Mysql
select email, count(*) as c FROM orders GROUP BY email
Solution 2 - Mysql
SELECT column_name, COUNT(column_name)
FROM table_name
GROUP BY column_name
Solution 3 - Mysql
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.
You can also use the group by function with a good number of other function define by MySQL (see the above link).
mysql> SELECT student_name, AVG(test_score)
-> FROM student
-> GROUP BY student_name;
Solution 4 - Mysql
select name, count(*) from table group by name;
i think should do it