CODEWITHSUNDEEP
sqlsql-servert-sql
Ezio Farenze
Ezio Farenze

Reputation: 89

How can I count and get the highest result from a table?

My table is:

id | candidate | position      | partylist
---+-----------+---------------|----------
1  | Bryan     | President     | LEAP
2  | Miko      | Vice President| SHARE
5  | Nico      | Vice President| LEAP
3  | Miko      | Vice President| SHARE
4  | Bryan     | President     | LEAP
6  | Miko      | Vice President| SHARE
7  | Bryan     | President     | LEAP
7  | Joe       | President     | SHARE

I need to count the votes and get the highest result from each position and the result would be:

candidate | position       | partylist | votes
----------+----------------+-----------+------
Bryan     | President      | LEAP      | 3
Miko      | Vice President | SHARE     | 3

I only tried this:

SELECT candidate
    , partylist
    , COUNT(*) AS votes
FROM tblvotes
WHERE position = 'President'
GROUP BY candidate
    , partylist
ORDER BY votes DESC

I don't know how to get the same as the result that I want.

Upvotes: 1

Views: 108

Answers (1)

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

If you are looking at per Position manually

select top 1 * from
(
    SELECT candidate, partylist, COUNT(*) AS Votes
    FROM tblvotes
    WHERE position = 'President'
    GROUP BY candidate, partylist
) res
order by Votes desc

if you are looking to display the whole result with the top votes then try below

;with mycte as 
(SELECT candidate, partylist, position, COUNT(*) AS Votes
FROM tblvotes
GROUP BY candidate, partylist , position
),
myRanking as 
(
select 
rank() over (partition by position order by votes desc) pos
,candidate, partylist, position, votes
from myCte
)
select * from myRanking where pos = 1

Upvotes: 1

Related Questions