mysqlsqlvoting-system

SQL Getting the party with highest votes


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


Solution

  • 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