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

`i.e. this does not work` --- explain it in details please. "Doesn't work" is not helpful at all — zerkms, Apr 17 '12 at 22:13
Thanks. Will do I don't know what came over me when I asked it. — Mahmoud Al-Qudsi, May 06 '12 at 10:38

score 118 · Accepted Answer · edited May 06 '12 at 10:33

118

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

edited May 06 '12 at 10:33

Cody Gray - on strike

239,200
50
490
574

answered Apr 17 '12 at 22:13

Nesim Razon

9,684
3
36
48

Apparently I had misread the results. Including the "email" in there made it clearer what data I was seeing. There really was nothing wrong with the query I have in my post. Thanks. – Mahmoud Al-Qudsi Apr 18 '12 at 01:06
2

order is a good ideia, so my contribuition: select email, count(*) as c FROM orders GROUP BY email ORDER BY c DESC; – ademar111190 Jan 16 '14 at 19:52

score 26 · Answer 2 · answered Apr 17 '12 at 22:15

26

SELECT column_name, COUNT(column_name)
FROM table_name
GROUP BY column_name

answered Apr 17 '12 at 22:15

garnertb

9,454
36
38

score 2 · Answer 3 · answered Apr 17 '12 at 22:18

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> SELECT student_name, AVG(test_score)
    ->        FROM student
    ->        GROUP BY student_name;

score 1 · Answer 4 · answered Apr 17 '12 at 22:14

1

select name, count(*) from table group by name;

i think should do it

answered Apr 17 '12 at 22:14

davethecoder

3,856
4
35
66

Count number of times value appears in particular column in MySQL

4 Answers4

Linked

Related