MySql：选择查询

Question

That is my mat_likes tables:

mat_id| yes| no
  1   | 1  | 0
  1   | 1  | 0
  1   | 1  | 0
  2   | 1  | 0
  3   | 1  | 0
  4   | 1  | 0
  4   | 1  | 0

How can I select mat_id of those materials, which have the most yes = 1. Еxample of subtracting the results:

1. mat_id = 1, because have total yes = 3
2. mat_id = 4, because have total yes = 2
3. mat_id = 3, because have total yes = 1
4. mat_id = 2, because have total yes = 1

Answer 1

SELECT mat_id,SUM(yes) yes_sum 
FROM mat_likes 
GROUP BY mat_id 
ORDER BY yes_sum DESC

will return a result set with the sum of yes's for each mat_id, with the greatest sums on top!

edit: added mat_id to the field list