selecting items count in mysql

I've a table like the following,

id    ip                 rating

1    192.161.0.1   3

1    192.161.0.2   4

1    192.161.0.4   3

2    192.161.0.5   1

and i need the result somethin like,

id    rating    count

1      3     开发者_开发问答      2

1      4           1

2      1           1

is it possible in mysql?

GROUP BY can be applied to multiple columns at once. Try:

SELECT id, rating, COUNT(id) AS count
FROM yourtable
GROUP BY id, rating
ORDER BY id, rating

SELECT `id`, `rating`, COUNT(`id`) AS `count` FROM `table` GROUP BY `id`, `rating`.

You should normalise your table more.

Query should be like this

SELECT id, rating, COUNT(rating) AS count
FROM yourtable
GROUP BY  id,rating
ORDER BY id, rating