Get sum of unique trades and the sum of the associated payments in SQL one to many relatioship-CodePudding

I have two tables trades and payments whereas single trade may have 0...* (none or many) payments.

I want to get the sum of all trade amounts and the sum of all associated payment amounts, for a trade belonging to a specific client/user.

Here is the query that gives the result of all trades and payments, BUT one of trade_amount is duplicated (the one for trade_id = 3) because there are two payments made against that trade:

SELECT t.id          AS trade_id,
       t.sell_amount AS trade_amount,
       p.id          AS pay_id,
       p.trade_id    AS pay_trade_id,
       p.amount      AS pay_amount
  FROM trades AS t
  JOIN payments AS p
    ON p.trade_id = t.id
 WHERE t.client_id = 88

and here is the result: trade_id trade_amount pay_id pay_trade_id pay_amount

trade_id | trade_amount | pay_id | pay_trade_id | pay_amount
1        | 10           | 1      | 1            | 10
2        | 20           | 2      | 2            | 20
3        | 30           | 3      | 3            | 10
3        | 30           | 4      | 3            | 10

I can't obtain the correct SUM of the trade_amounts because it would add the amount of trade_id = 3 twice, hence the sum would be 90 ❌. That is not what I want! The desired result would be the sum of all DISTINCT trades, hence SUM of all distinct trade amounts 10 20 30 = 60✅

As for the payments, the sum is pretty straightforward since there aren't any repetitions/duplicates of payments, hence SUM(pay_amount) would yield to correct result 10 20 10 10=50✅

Here is a fiddle to play with.

I would like to get a result that would yield desired sums:

trade_amount_sum = 60 (but I get 90)

pay_amount_sum = 50 (and I get 50 and that is correct)

CodePudding user response：

When you JOIN two tables, you get all combinations of rows matching the ON conditions. When table a has one row that matches multiple rows in table b you'll get multiple copies of the data from tables a in your results. That leads to a combinatorial explosion. No fun. Baffling.

So you need to use subqueries that generate only one possible combination of rows.

The aggregating subquery to get exactly one row per trades.id is this.

       SELECT id, client_id, 
              SUM(sell_amount) AS sell_amount
         FROM trades
        GROUP BY id, client_id

Then join it to your other table instead of your detail trades table.

SELECT t.id          AS trade_id,
       t.sell_amount AS trade_amount,
       p.id          AS pay_id,
       p.trade_id    AS pay_trade_id,
       p.amount      AS pay_amount
  FROM (  SELECT  id, 
                  client_id,
                  SUM(sell_amount) AS sell_amount
             FROM trades
            GROUP BY id, client_id
       ) AS t
  JOIN payments AS p
    ON p.trade_id = t.id
 WHERE t.client_id = 88

When generating these sorts of reports, you need to aggregate your detail tables separately to get a single row per report row. Otherwise you'll get the combinatorial explosion ... multiples of the correct COUNT and SUM values.

CodePudding user response：

You need to sum up only distinct trade amounts like this:

  SELECT 
       SUM(DISTINCT(t.sell_amount)) AS trade_amount_sum,
       SUM(p.amount)      AS pay_amount_sum
  FROM trades AS t
  JOIN payments AS p
    ON p.trade_id = t.id
 WHERE t.client_id = 88;