Aggregate calculation query on single SQL table

Question

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 | 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 a contribution a payer made towards a payment. The pays field specifies whether that payer should be included in the payment.

The total cost of a payment is the sum of amount for a given payment_id. So if I wanted to see how much payment 2 cost I would run:

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

Likewise the total amount a given payer (say, payer 1) has contributed is:

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

Now, what I want to do is use the concept of fair share. Fair share is the total cost of the payment divided by the number of payers who have pay=1 for that payment. The simplest way I can express this is with a sub-query:

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;

For a given payer and payment their fair share is defined as:

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

My question is that I want a query to get the total fair share for each payer based on the fair share of each payment and whether or not they are included in the payment. (If pays=false then their fair share is 0)

Based on the above data this is the kind of result I'm after for the 2 payers:

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

Is there a way to achieve this in a single query or must I do some looping of result sets? I am agnostic on RDMS but something MySQL-like is good.

Should be doable with a subquery. – PM 77-1 Mar 18 '14 at 13:12 — PM 77-1, Mar 18 '14 at 13:12

score 2 · Accepted Answer · answered Mar 18 '14 at 13:24

2

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

answered Mar 18 '14 at 13:24

MatBailie

83,401
18
103
137

Wow, nice job. So the query makes a non-existant table `payments` to hold the single shares? I already have a `payments` table so I guess it makes sense to call it something else but it works! Amount is stored as decimal with >2 decimal places so as far as rounding is concerned amounts are rounded to 2 dp in views so there will be slight error which I don't mind about at the moment. Been trying to get this for weeks so appreciate the help. – harryg Mar 18 '14 at 14:44
@harryg - It doesn't actually create a table, it's just a sub-query. Something similar to a view that only exists for the duration of the query. But choosing names that don't conflict is often good practice to aid maintenance. – MatBailie Mar 18 '14 at 14:51
@harryg - Be careful of holding amounts to more than 2 decimal places in order to "get around" the rounding errors. It can cause money to "disappear" slowly over time. One option in the case of `10.00 / 3` is to give two people `3.33` and one person `3.34`. another is to create a "fund" that gathers the spare `0.01` to give away in some way. But it's generally considered very poor practice to allow people to have `3.333333` *(because to can't have 0.003333 of a currency)* while also allowing the `0.000001`'s to "vanish from the accounts". – MatBailie Mar 18 '14 at 14:55
Fair points, thanks for your advice. I will implement this query in my app (need to try and replicate the query in Laravel/Eloquent). Once I have my head around things I will work on the rounding. As the data is calculated dynamically (I.e. results are not stored) I'm assuming rounding can work both ways and will even out over time. – harryg Mar 18 '14 at 15:26
@harryg - That's a dangerous assumption for which many accountants will have a field day with you. But depending on your context that may not matter to you, I have no idea. That said, I agree that getting it working is pretty important as a first step ;) Good luck. – MatBailie Mar 18 '14 at 15:36
One extra thing: how would I limit my query to only select records for a certain user_id, assuming there is a joined `payers` table (`payers_payments.payer_id = payers.id`) that contains a `user_id` foreign key? I'm having trouble adding the join to your query – harryg Mar 19 '14 at 22:42
Could you open a new question, and give an example of the data, the query you tried, and your problems? Add a comment to me and I'll have a look later today, only have access via my phone right now :) – MatBailie Mar 20 '14 at 09:40
Question here: http://stackoverflow.com/q/22520366/1606323. I feel it is due to my nested joins not being liked by SQLite as mentioned in an answer so will check it later. – harryg Mar 20 '14 at 10:47

Aggregate calculation query on single SQL table

1 Answers1

Linked