Mysql Group by second column if first column is equal?

Question

I have a table that looks like this

id | rating
1  | 1
1  | 3
1  | 1
1  | 2
2  | 3
2  | 3
2  | 1

etc,you get the idea.

Anyway, I want to end up with this result set

id | rating | num
1  | 1      | 2
1  | 3      | 1
1  | 2      | 1
2  | 3      | 2
2  | 1      | 1

So, what I want to do is group the rating's together as long as the id is the same!

I thought I could just do group by id, rating and just assumed it would group only if both columns were the same, but it doesn't it groups if either column is the same so I end up with

id | rating | num
1  | 1      | 7

How can I solve this?

Answer 1

you could try

SELECT id,rating,COUNT(rating) AS num 
FROM table1 
GROUP BY id,rating
ORDER BY id ASC

SQLFiddle Demo

Answer 2

I don't understand the issue you are having. This query works for me:

select 
  id, rating, COUNT(rating) as num 
FROM rating 
GROUP BY id, rating 
ORDER BY id, rating ASC;

And, just to be thorough, here is my entire test session:

mysql> create table rating (id int, rating int);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into rating values (1,1),(1,3),(1,1),(1,2),(2,3),(2,3),(2,1);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select id, rating, COUNT(rating) as num FROM rating GROUP BY id, rating;
+------+--------+-----+
| id   | rating | num |
+------+--------+-----+
|    1 |      1 |   2 | 
|    1 |      2 |   1 | 
|    1 |      3 |   1 | 
|    2 |      1 |   1 | 
|    2 |      3 |   2 | 
+------+--------+-----+
5 rows in set (0.00 sec)

Answer 3

SELECT id,rating,count(rating) from table group by id,rating