[SOLVED] SQL Finding a winner for election

Issue

I am working on this problem below, but I am stuck.

How can I find what party won what region (constituency)?

Can you help me modify my query?

I am adding the create table and insert into commands for testing purposes below;

CREATE TABLE qbiz_candidates
(id  int PRIMARY KEY,
 gender text,
 age    int,
 party   TEXT
 );


INSERT INTO qbiz_candidates (id, gender, age, party)
    VALUES
     (1, 'M', 50, 'A'),
     (2, 'M', 50, 'B'),
     (3, 'F', 50, 'A'),
     (4, 'M', 50, 'B'),
     (5, 'F', 50, 'A'),
     (6, 'M', 50, 'B');

CREATE TABLE qbiz_results
(constituency_id  int ,
 candidate_id int ,
 votes int,
 PRIMARY KEY (constituency_id, candidate_id)
 );

INSERT INTO qbiz_results (constituency_id, candidate_id, votes)
    VALUES
     (1, 1, 10),
     (1, 2, 5),
     (1, 3, 10),
     (1, 4, 5),
     (1, 5, 10),
     (1, 6, 5),
     (2, 1, 5),
     (2, 2, 10),
     (2, 3, 5),
     (2, 4, 10),
     (2, 5, 5),
     (2, 6, 10);

My query:

select c1.party, b.constituency_id, max(b.total_votes)
from qbiz_candidates as c1
join (select c.party,r.constituency_id, sum(r.votes) as total_votes
from qbiz_candidates as c
join qbiz_results as r
    on r.candidate_id = c.id
group by r.constituency_id,c.party) b 
on b. party = c1.party
group by c1.party, b.constituency_id

Expected output:

A 1
B 2

Meaning party A won constituency 1 and party B won constituency 2.

Solution

Option 1 with dense_rank() or row_number(), here is the demo.

select
    party,
    constituency_id
from
(
    select
      party,
      constituency_id,
      sum(votes) as total_votes,
      dense_rank() over (partition by party order by sum(votes) desc) as rnk
  from qbiz_candidates qc
  join qbiz_results qr
  on qc.id = qr.candidate_id
  group by
      party,
      constituency_id
) val
where rnk = 1

Output:

*-----------------------*
|party | constituency_id|
*-----------------------*
| A    |  1             |
| B    |  2             |
*-----------------------*

Option 2 with union all, here is the demo.

 (
   select
      party,
      constituency_id
  from qbiz_candidates qc
  join qbiz_results qr
  on qc.id = qr.candidate_id
  where constituency_id = 1
  group by
      party,
      constituency_id
order by
    sum(votes) desc
limit 1
)
union all
(
 select
      party,
      constituency_id
  from qbiz_candidates qc
  join qbiz_results qr
  on qc.id = qr.candidate_id
  where constituency_id = 2
  group by
      party,
      constituency_id
order by
    sum(votes) desc
limit 1
)

Option 3 with group_concat(). here is the demo.

select
    party,
    SUBSTRING_INDEX(GROUP_CONCAT(constituency_id order by total_votes desc), ',', 1) as constituency_id
from
(
  select
      party,
      constituency_id,
      sum(votes) as total_votes
  from qbiz_candidates qc
  join qbiz_results qr
  on qc.id = qr.candidate_id
  group by
      party,
      constituency_id

) t
group by
    party

Answered By – zealous

Answer Checked By – Katrina (BugsFixing Volunteer)