Aggregate Calculation Query On Single Sql Table

In my database I have a table, payers_payments, consisting of some foreign keys (payer_id, payment_id) and some pivot fields (amount, pays). | id | payer_id | payment_id | amount |

Solution 1:

I would start by writing a query that works what a single share of a payment is. That is, per payment_id, the sum of all the amounts, divided by the number of people it needs to pay. That result can then be joined back to the original data.

SELECT
  payers_payments.payer_id,
  SUM(payers_payments.amount                      )   AS total_paid,
  SUM(payers_payments.pays * payments.single_share)   AS fair_share
FROM
  payers_payments
INNER JOIN
(
  SELECT
    payment_id,
    SUM(amount) / SUM(pays)   AS single_share
  FROM
    payers_payments
  GROUPBY
    payment_id
)
  AS payments
    ON  payers_payments.payment_id = payments.payment_id
GROUPBY
   payers_payments.payer_id

It will be of benefit to have indexes on both (payment_id) and (payer_id).

It will be of benefit to have the amount field in a DECIMAL data-type, though you need to consider what you want to do with rounding. (A total payment of 10.00 needs to be divided three ways, 3.33 each and then what do you want to happen to the spare 0.01?)