I have a mysql table for votes. there's an id开发者_StackOverflow
, 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!
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
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.
Use GROUP BY
:
SELECT project_id, SUM(vote) as votes
FROM table
GROUP BY project_id
ORDER BY votes DESC
try this:
SELECT
ProjectID,SUM(Vote)
FROM YourTable
GROUP BY ProjectID
ORDER BY Sum(Vote) DESC
精彩评论