SQL in MS Access max of sum

Question

SELECT  
    tableResults.PoliticalParty, 
    MAX(PoliticalPartyVotes.TotalVotes) AS [EX11]
FROM
    (SELECT 
         tableResults.PoliticalParty, 
         SUM(INT(tableResults.Votes)) AS TotalVotes
     FROM tableResults 
     GROUP BY tableResults.PoliticalParty) AS PoliticalPartyVotes;

This doesn't work, tableResults.PoliticalParty not showing one one result with max.

tag your database, such as SQL server, My SQL? and also your expected output in tabular form. — zealous, Apr 26 '20 at 22:33

score 0 · Accepted Answer · answered Apr 26 '20 at 22:35

If you want the PoliticalParty with most votes, you can use ORDER BY and TOP (1) in your existing aggregate query:

SELECT TOP (1) PoliticalParty, Sum(INT(Votes)) AS TotalVotes
FROM tableResults 
GROUP BY tableResults.PoliticalParty
ORDER BY Sum(INT(Votes)) DESC

To allow top ties (ie, two PoliticalParty having the same, maximum total of votes), the you can use TOP (1) WITH TIES instead.

SQL in MS Access max of sum

1 Answers1