Problem with SQL: combining a JOIN, SUM and MAX-CodePudding

I want to construct a query that provides me of the name of the party which received the most votes on their councilors.

The database is constructed in the following manner: Party (PartyID, NameParty, StartDate, AmountofSeats) Councilors (LidID, NameCouncilor, Council, Party, AmountofVotes)

Party in Councilors = PartyID in Party.

I wrote the following queries but both are not able to provide me with the correct party-name. Could someone help me out: what did I do wrong?

This code brings me the wrong party-name (it is not the highest amount of votes in total).

SELECT s.NameParty, s.SumVotes
    FROM (SELECT Party.NameParty, SUM(Councilors.AmountofVotes) SumVotes, Party.PartyID, Council.Party
        FROM Councilors
        JOIN Party on Party.PartyID = Councilors.Partij
        GROUP BY Party.PartyID) s
        WHERE s.SumVotes = (SELECT MAX(AmountofVotes)
                        FROM Councilors w1
                        WHERE w1.Party = s.PartyID);

Attempt2: This code gives me an error which has to do with the >= ALL but I do not understand why.

 SELECT s.NameParty, s.SumVotes
    FROM (SELECT Party.NameParty, SUM(Councilors.AmountofVotes) SumVotes, Party.PartyID, Councilors.Party
        FROM Councilors
        JOIN Partij on Party.PartyID = Councilors.Party
        GROUP BY Party.PartyID) s
        HAVING SUM(Councilors.AmountofVotes) >= ALL (
                SELECT SUM(Councilors.AmountofVotes) 
                FROM Councilors
                WHERE Councilors.Party = s.PartyID)

CodePudding user response：

Now is a good time to learn about analytic functions. Use RANK() here:

WITH cte AS (
    SELECT p.PartyId, p.NameParty, SUM(c.AmountofVotes) SumVotes,
           RANK() OVER (ORDER BY SUM(c.AmountofVotes) DESC) rnk
    FROM Party p
    INNER JOIN Councilors c ON p.PartyID = c.Party
    GROUP BY p.PartyID, p.NameParty
)

SELECT PartyId, NameParty, SumVotes
FROM cte
WHERE rnk = 1;

Note that this approach is concise and is also robust to the possibility of ties between two or more parties having the most votes.