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: mat_id = 1 , because have total yes = 3 mat_id = 4 , because have total yes = 2 mat_id = 3 , because have total yes = 1 mat_id = 2 , because have total yes = 1

Reputation: 259

MySql: Select query

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:

Upvotes: 0

Reputation: 5607

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

Upvotes: 3

Reputation: 47462

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

Upvotes: 0

Reputation: 1531

Upvotes: 0