Consulta de cálculo agregado en una sola tabla SQL

En mi database tengo una tabla, payers_payments , que consta de algunas foreign keys ( payer_id , payment_id ) y algunos campos pivote ( 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 | 

Cada fila representa una contribución hecha por un payer para un payment . El campo de pays especifica si ese payer debe includese en el pago.

El costo total de un payment es la sum del amount de un payment_id determinado. Entonces, si quisiera ver cuánto pagaría, me costaría 2:

 SELECT SUM(amount) AS sumOfPayment FROM payers_payments WHERE payment_id=2; 

Del mismo modo, la cantidad total que un payer determinado (por ejemplo, el pagador 1) ha contribuido es:

 SELECT SUM(amount) AS sumOfPayment FROM payers_payments WHERE payer_id=1; 

Ahora, lo que quiero hacer es usar el concepto de participación justa . La parte justa es el costo total del pago dividido por el número de payers que tienen un pay=1 por ese pago. La forma más sencilla en que puedo express esto es con una subconsulta:

 SELECT SUM(payers_payments.amount) / (SELECT count(*) FROM payers_payments WHERE pays AND payers_payments.payment_id = 3 ) AS FairShare FROM payers_payments WHERE payment_id=3 GROUP BY payers_payments.payment_id; 

Para un payer determinado y el payment su parte equitativa se define como:

 SELECT IF(pays, FairShare, 0) as payerFairShare FROM payers_payments WHERE payer_id = 1 AND payment_id=3; --FairShare is the query as above 

Mi pregunta es que quiero una consulta para get la parte equitativa total de cada payer function de la parte justa de cada pago y de si están incluidos o no en el pago. (Si pays=false entonces su parte justa es 0)

Según los datos anteriores, este es el tipo de resultado que busco para los 2 pagadores:

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

¿Hay alguna manera de lograr esto en una sola consulta o debo hacer algún bucle de sets de resultados? Soy agnóstico en RDMS pero algo parecido a MySQL es bueno.

Comenzaría escribiendo una consulta que funciona de la misma manera que una parte de un pago. Es decir, por payment_id , la sum de todos los importes, dividida por la cantidad de personas que debe pagar. Ese resultado se puede volver a unir a los datos originales.

 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 GROUP BY payment_id ) AS payments ON payers_payments.payment_id = payments.payment_id GROUP BY payers_payments.payer_id 

Será beneficioso tener índices en ambos (payment_id) y (payer_id) .

Será beneficioso tener el campo de amount en un tipo de datos DECIMAL, aunque debe considerar lo que quiere hacer con el networkingondeo. (Un pago total de 10.00 debe dividirse de tres forms, 3.33 cada una y luego, ¿qué desea que le pase al repuesto 0.01?)