locked
Query To Get The Highest Value By Count RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    User-698989805 posted
    Hello Friends! I've the following table structure:

    ID - VoterId - Candidate
    1 - 1002 - 2000
    2 - 1003 - 2000
    3 - 1004 - 2001
    4 - 1005 - 2000
    5 - 1006 - 2001
    6 - 1007 - 2002

    So what I like to do is to get the highest count value like candidate 2000 has 3 votes/counts, so the output would be as follows:

    ID - Candidate - Votes - Status
    1 - 2000 - 3 - Winner
    2 - 2001 - 2 - Loser
    3 - 2002 - 1 - Loser

    Again the output could be like this if any candidate has the same no. of votes:

    ID - Candidate - Votes - Status
    1 - 2000 - 2 - Winner
    2 - 2001 - 2 - Winner
    3 - 2002 - 1 - Loser

    I've tried the below Sql query that gives only votes/count 0 as 'Loser' and values greater than 0 as 'Winner':

    SELECT Candidate, COUNT(Candidate) AS Votes, CASE Candidate WHEN MAX(Candidate) THEN 'Winner' ELSE 'Loser' FROM VoteCast GROUP BY Candidate

    I would expect some ideas from the experts to do it in the correct way. Thanks.
    Wednesday, August 10, 2016 6:37 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote
    User77042963 posted
    create table VoteCast  (ID int identity(1,1),VoterId int, Candidate int)
Insert into VoteCast  values  (1002,2000), (1003,2000),(1004,2001),(1005,2000),(1006,2001),(1007,2002)
;with mycte as 
(SELECT Candidate, count(*) AS Votes FROM VoteCast GROUP BY Candidate
)
select Candidate, Votes
, Case WHen dense_rank()Over(Order by Votes DESC) =1 Then 'Winner' Else 'Loser' End  Status
from mycte

drop table VoteCast

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 10, 2016 8:17 PM