Select count for each distinct row (mysql and php)

Question

I am using mysql and php.

I have a table with one column. I can show unique rows by:

select distinct id from id_table

This might show

What I want to do is show the number of 1's, 2's, etc there are.

I could do

select count(id) from id_table where id = 1

But then I have to run a query for every... single... row... And with hundreds of thousands of rows, not good.

Is there a way to return an array of the counts of each distinct item

Data

1, 1, 1, 2, 3, 3, 5

Results

3, 1, 2, 1

Thanks

Zohaib · Accepted Answer · 2011-11-25 03:49:34Z

29

select id, count(id)
from table
group by id

If only want count of ids, then

select count(id)
from table
group by id

Here is a simple tutorial of group by clause

answered Nov 25, 2011 at 3:49

Zohaib

7,1263 gold badges29 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1