SQL Getting the party with highest votes

Question

I am trying to make a voting system through a SQL server, and I can't get it right. What I am trying to do is get the party with the highest amount of votes.

SELECT COUNT(*) 
FROM Vote 
    INNER JOIN Members ON Vote.Voted = Members.PartyName 
WHERE (PartyName is the biggest one)

I expect something like [DEMS][8], or at the very least, the party name of the party with the highest votes.

enter image description here

Does one assume that something in your data corresponds to "party" and to "votes"? It is not obvious. — Gordon Linoff, Feb 19 '20 at 22:16

Simon Notley · Accepted Answer · 2020-02-20T08:51:12.153

0

Rather than using a WHERE clause you need to use whatever the syntax is for the top record in your SQL dialect. You also need to group by partijnaam. This is a bit of a guess as I don;t know your exact data structure.

Postgres/MySQL

SELECT PartijNaam, COUNT(*) 
FROM stem 
    INNER JOIN leden ON stem.Gestemt = Leden.lidnummer 
GROUP BY PartijNaam
ORDER BY 2 DESC
LIMIT 1

SQL Server

SELECT TOP 1 PartijNaam, COUNT(*) 
FROM stem 
    INNER JOIN leden ON stem.Gestemt = Leden.lidnummer
GROUP BY PartijNaam
ORDER BY 2 DESC

edited Feb 20 '20 at 08:51

answered Feb 19 '20 at 22:23

Simon Notley

2,070
3
12
18

I have added a picture of the 2 tables and their relation. stem meaning vote and leden meaning member gestemt means the member number they have voted on. also i have tried what you suggested but it gave blank results – Rick W Feb 19 '20 at 22:41
Looks like the join is wrong then. Still guessing somewhat but it seems like you want to join gestemt to lidnumber, are they both the member number? – Simon Notley Feb 19 '20 at 22:57
yes that is becouse when you have voted you vote for a member of the party so they are linked. when i want to check the votes for a member you just count the "gestemt" – Rick W Feb 19 '20 at 23:04

score 0 · Answer 2 · answered Feb 20 '20 at 08:57

0

SELECT PartijNaam FROM leden INNER JOIN stem ON leden.LidNummer = stem.Gestemt GROUP BY PartijNaam ORDER BY COUNT(gestemt) DESC LIMIT 1;

answered Feb 20 '20 at 08:57

Rick W

75
7

score 0 · Answer 3 · answered Feb 20 '20 at 08:58

0

try this:

  SELECT  PartijNaam, COUNT(*)as vote 
    FROM stem 
        INNER JOIN leden ON stem.Gestemt = Leden.lidnummer
    GROUP BY PartijNaam
    ORDER BY DESC

answered Feb 20 '20 at 08:58

Vishal Parmar

524
7
27

SQL Getting the party with highest votes

3 Answers3