mysql - Aggregate calculation query on single SQL table -

in database have table, payers_payments, consisting of foreign keys (payer_id, payment_id) , pivot fields (amount, pays).

| id | payer_id | payment_id | amount | pays | |----|----------|------------|--------|------| | 1  | 1        | 1          | 20     | 0    | | 2  | 2        | 1          | 23     | 1    | | 4  | 1        | 2          | 14     | 1    | | 5  | 2        | 2          | 17     | 1    | | 6  | 1        | 3          | 10     | 1    | | 7  | 2        | 3          | 0      | 0    | 

each row represents contribution payer made towards payment. pays field specifies whether payer should included in payment.

the total cost of payment sum of amount given payment_id. if wanted see how payment 2 cost run:

select sum(amount) sumofpayment    payers_payments    payment_id=2; 

likewise total amount given payer (say, payer 1) has contributed is:

select sum(amount) sumofpayment    payers_payments   payer_id=1; 

now, want use concept of fair share. fair share total cost of payment divided number of payers have pay=1 payment. simplest way can express sub-query:

select sum(payers_payments.amount) / (select count(*)                                          payers_payments                                         pays                                               , payers_payments.payment_id = 3                                              ) fairshare   payers_payments  payment_id=3 group         payers_payments.payment_id; 

for given payer , payment fair share defined as:

select if(pays, fairshare, 0) payerfairshare    payers_payments   payer_id = 1         , payment_id=3; --fairshare query above 

my question want query total fair share each payer based on fair share of each payment , whether or not included in payment. (if pays=false fair share 0)

based on above data kind of result i'm after 2 payers:

| payer_id | total_paid | fair_share | |----------|------------|------------| | 1        | 44         | 25.5       | | 2        | 40         | 58.5       | 

is there way achieve in single query or must looping of result sets? agnostic on rdms mysql-like good.

i start writing query works single share of payment is. is, per payment_id, sum of amounts, divided number of people needs pay. result can joined original data.

select   payers_payments.payer_id,   sum(payers_payments.amount                      )   total_paid,   sum(payers_payments.pays * payments.single_share)   fair_share   payers_payments inner join (   select     payment_id,     sum(amount) / sum(pays)   single_share       payers_payments   group     payment_id )   payments     on  payers_payments.payment_id = payments.payment_id group    payers_payments.payer_id 

it of benefit have indexes on both (payment_id) , (payer_id).

it of benefit have amount field in decimal data-type, though need consider want rounding. (a total payment of 10.00 needs divided 3 ways, 3.33 each , want happen spare 0.01?)