MySQL Query order by numer of rows?

Question

I have a mysql table for votes. there's an id, a project_id and a vote field (which is 1 if a specific project is voted). now i want to generate a ranking from those entries. is there a way to get the number of votes for each project_id and automatically sort the entries by the number of TRUE votes of a project with a single mysql query? Or do you know a php way?

e.g.

ID | Project ID | Vote
-----------------------
1  | 2          | 1
2  | 2          | 1
3  | 1          | 1

==>

Project Nr. 2 has 2 Votes

Project Nr. 1 has 1 Vote

Thanks in advance!

Answer 1

Use GROUP BY:

SELECT project_id, SUM(vote) as votes 
FROM table 
GROUP BY project_id 
ORDER BY votes DESC

Answer 2

Select project_id, Sum( vote ) project_vote
From votes
Group By project_id
Order By project_vote Desc

GROUP BY allows you to get the sum of votes per project_id, the ORDER BY DESC puts the highest votes first.

Answer 3

SELECT 
   `project_id`, SUM(`vote`) AS vote_count 
FROM 
   `table_name` 
GROUP BY `project_id`
ORDER BY vote_count DESC

aliasing the SUM will help you to find the column in php later if you fetch as an associative array

Answer 4

try this:

SELECT 
    ProjectID,SUM(Vote) 
    FROM YourTable 
    GROUP BY ProjectID 
    ORDER BY Sum(Vote) DESC