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
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
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.