[thelist] SQL Query to count votes and order by highest

[Phil Turmel]

Ken Snyder wrote:
> Chris Byrd wrote:
> 
>>I'm part way through creating an online voting system for various school
>>'awards' and I need a query to count the number of votes for a specific
>>'award' and list the results in descending order 
>> 
>>Each time there's a vote, a row is created in a 'vote' table
>> 
>>Amongst other fields in the 'vote' table are 'vote_to_student_id' and
>>'vote_award_id'
>> 
>>Basically, I need to be able to run a query for each 'award_id' that
>>will return (in descending order) the number of votes and each
>>corresponding vote_to_student_id
>>
>>  
> 
> This worked for me on MS SQL 2005.
> 
> SELECT vote_award_id, COUNT(vote_to_student_id) as VoteCount
> FROM vote
> GROUP BY vote_award_id
> ORDER BY COUNT(vote_to_student_id) DESC
> 
> -- Ken
> 
Ken,

While that would run, I don't think it gives the answer Chris is looking 
for.  It returns the list of elections, in descending order of voter 
turnout.

Chris,

Given @ID for a single award:

SELECT vote_to_student_id, COUNT(*) as VoteCount
FROM vote
WHERE vote_award_id = @ID
GROUP BY vote_to_student_id
ORDER BY VoteCount DESC

will likely be what you are after.

HTH,

Phil