开发者

MySQL Query order by numer of rows?

开发者 https://www.devze.com 2022-12-23 04:01 出处:网络
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 t

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
0

精彩评论

暂无评论...
验证码 换一张
取 消