How to implement the D'Hondt method to allocate seats to votes in SQL?

Question

I have a simple SQL (PostgreSQL or MySQL) table where rows represents parties while a column represents the number of votes they received on an election. I want to allocate the number of seats (n) to each party (i.e. to a new column) using the D'Hondt method.

What procedure (or function) should I write to do that?

Update: example for the desired output assumming 4 seats to allocate:

           votes    allocated_seats

party1     47000         2
party2     16000         1
party3     15900         1

Seats are allocated like that (D'Hondt method): calculate V/(s+1) for each party
where V: number of votes for the party
s: seats already allocated to the party

Party with the highest value receives a seat and the process starts over till all the seats are distibuted.

So in the example above:
Seat 1:
party1 47000/(0+1)=47000
party2 16000/(0+1)=16000
party3 15900/(0+1)=15900
party1 receives the seat

Seat 2:
party1 47000/(1+1)=23500
party2 16000/(0+1)=16000
party3 15900/(0+1)=15900
party1 receives the seat

Seat 3:
party1 47000/(2+1)=15666
party2 16000/(0+1)=16000
party3 15900/(0+1)=15900
party2 receives the seat

Seat 4:
party1 47000/(2+1)=15666
party2 16000/(1+1)=8000
party3 15900/(0+1)=15900
party3 receives the seat

. . If you want an answer to your question, I would recommend that you show sample data and the process for solving it. — Gordon Linoff, Jan 29 '15 at 13:31
When it comes to writing procedures the DBMS being used makes a **big** difference. So you will need to decide if you want to use MySQL or Postgres — , Jan 29 '15 at 13:32
@GordonLinoff : You are right. The linked wikipedia page shows a good example. — Viktor, Jan 29 '15 at 13:39
@a_horse_with_no_name : That's true. Currently we are using postgresql but there is a chance that we will move to mysql. — Viktor, Jan 29 '15 at 13:41
Victor, for the avoidance of doubt: this question is likely to close in its present condition, since there is no implementation attempt around which a concrete question can be posed. Can you give it a go, or search on the web for an existing implementation, and go from there? — halfer, Jan 29 '15 at 13:54

score 1 · Accepted Answer · edited May 23 '17 at 10:26

In PostgreSQL, you don't need a function for that, a simple update can do it:

update dhont
set    seats = coalesce(calculated.seats, 0)
from   dhont as parties
left join (
  select party, count(*) as seats
  from (
    select     party
    from       dhont
    cross join generate_series(1, :seat_count) as divisor
    order by   cast(votes as decimal) / divisor desc
    limit      :seat_count
  ) as seat_labels
  group by party
) as calculated on parties.party = calculated.party
where parties.party = dhont.party

SQLFiddle

Note: these are sample data. For actual join conditions, please use your table's primary key (or at least a unique key), not just the party's name.

In theory, the same can work in MySQL too, but with an ugly workaround.

Thanks, it looks like a simple and elegant solution for my problem! — Viktor, Jan 29 '15 at 14:48

How to implement the D'Hondt method to allocate seats to votes in SQL?

1 Answers1