0

I have 3 tables each containing election_id election_year election_type (which is a string), party_name(also a string) party_ID, and the last one has Results_ID(equal to election_id) Results_party(equal to party_ID) and lastly ballots, my query has to return max number of ballots each party has ever gotten for the years in consideration for example(also desired output):

Name Year Ballots
Party A 2000 105
Party B 2000 95
Party C 2004 50
Party C 2008 50

I tried this

SELECT Party.party_name,Elections.election_year,Results.Results_ballots
FROM ((Elections 
INNER JOIN Results 
   on Elections.election_ID=Results.Results_Elections 
   AND Elections.election_Type='Regional') 
INNER JOIN Party on Party.party_ID=Results.Results_Party)

but this gives me all the parties and all the elections with all the ballots, I want max ballot count for each one any ideas are appreciated, I'm very new to MySQL

EDIT: also tried this, which missed the last row of the table

FROM Party as p, Elections as e, Results as r
where p.party_ID=r.Results_Party and e.election_Type='Regional'  AND r.Results_Elections=e.Elections_ID
group by p.party_name

So it looked like this

Name Year Ballots
Party A 2000 105
Party B 2000 95
Party C 2004 50
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Klaudio
  • 23
  • 4
  • Please check [aggregate functions](https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions-and-modifiers.html) in the docs. – astentx Nov 13 '21 at 22:11
  • show us your desired output – eshirvana Nov 13 '21 at 22:27
  • @eshirvana the desired output is the one I put on the original post – Klaudio Nov 13 '21 at 22:45
  • @astentx I actually did and got pretty close but it's missing an entry, the last one on the original post to be precise, which is of the same type just a different year, so it should take those with equal number of ballots too – Klaudio Nov 13 '21 at 22:47
  • @Klaudio you want to do 2 groups, `GROUP BY p.party_name, e.election_year` – Breezer Nov 13 '21 at 23:39
  • @Breezer it results in the full table still, with all the years and multiple ballot entries – Klaudio Nov 13 '21 at 23:46
  • @Klaudio did my answer work for you`?? – Breezer Nov 14 '21 at 01:25
  • Does this answer your question? [Get top n records for each group of grouped results](https://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results) – astentx Nov 14 '21 at 08:45

1 Answers1

0

If I understood you correctly, you have to do a subquery to get the max for each party.

this is what I came up with

SELECT * FROM Party c
LEFT JOIN Results b
ON b.Results_party=c.party_ID
AND b.ballots=(SELECT MAX(d.ballots) FROM Results d WHERE d.Results_Party=c.party_ID)
LEFT JOIN Elections a
ON a.Elections_ID=b.Results_ID
Breezer
  • 10,410
  • 6
  • 29
  • 50
  • This gets the rows right but I can't seem to group it by the columns of interest, the rows are there tho – Klaudio Nov 14 '21 at 09:38
  • Ok now I got the columns, it still says it's failing one test, but I suspect the tests I've been given are janky. But it seems fine to me. Thanks for the help, much appreciated. – Klaudio Nov 14 '21 at 09:43