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.

Answer 1

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

Answer 2

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

Answer 3

try this:

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