Question 113332 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Get sum of unique trades and the sum of the associated payments in SQL one to many relatioship

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)

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search