Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2013
    Posts
    13

    Question Multiple JOINS cause wrong sum

    Hello everyone i am trying to make a query with two LEFT JOINs and something is not working, i would love some help.
    Let me introduce you the tables i'm working on:

    Code:
    USERS
    ----------+-----------+
    | user_id | user_name |
    ----------+-----------+
    |    1    |   Jhon    |
    |    2    |   Paul    |
    |    3    |   George  |
    |    4    |   Ringo   |
    ----------+-----------+
    
    PAYMENT_TRANSACTIONS
    --------------+------------+
    | trx_user_id | trx_amount |
    --------------+------------+
    |      1      |   100.00   |
    |      1      |   -87.00   |
    |      3      |    55.30   |
    |      3      |    10.00   |
    --------------+------------+
    
    ORDERS
    ----------------+--------------+
    | order_user_id | order_amount |
    ----------------+--------------+
    |       3       |     4.00     |
    |       3       |    10.00     |
    |       1       |    15.00     |
    ----------------+--------------+
    I want to get a table with all the user names in a column, the sum of deposits and sum of withdrawals for each user in other two colums, and the sum of orders for each user in a fourth column. (widthdrawals are negative trx_amount and deposits are positive trx_amount)

    Following the example above the result would look something like:
    Code:
    ------------+----------+--------------+--------+
    | user_name | Deposits | widthdrawals | orders |
    ------------+----------+--------------+--------*
    |   Jhon    |  100.00  |    -87.00    |  15.55 |
    |   Paul    |   00.00  |     00.00    |  00.00 |
    |   George  |   65.30  |     00.00    |  25.00 |
    |   Ringo   |   00.00  |     00.00    |  00.00 |
    ------------+----------+--------------+--------+
    I wrote a query that is working fine but doesn't include the sum of orders for each user:

    Code:
    SELECT 
    	u.user_name,
    	SUM(CASE WHEN t.trx_amount>0 THEN t.trx_amount ELSE 0 END) AS deposits,
    	SUM(CASE WHEN t.trx_amount<0 THEN t.trx_amount ELSE 0 END) AS withdrawals
    FROM 
    	users AS u
    	LEFT JOIN payment_transactions AS t ON t.trx_user_id=u.user_id
    GROUP BY 
    	u.user_id
    When i try to include a second left join the sums are messed and all numbers lose any meaning:
    Code:
    SELECT 
    	u.user_name,
    	SUM(CASE WHEN t.trx_amount>0 THEN t.trx_amount ELSE 0 END) AS deposits,
    	SUM(CASE WHEN t.trx_amount<0 THEN t.trx_amount ELSE 0 END) AS withdrawals
    	SUM(o.order_amount)
    FROM 
    	users AS u
    	LEFT JOIN payment_transactions AS t ON t.trx_user_id=u.user_id
    	LEFT JOIN orders AS o ON o.order_user_id=u.user_id
    GROUP BY 
    	u.user_id
    As i said before, the first query is doing well but i have problems including the sum of orders for each user.
    Any help would be really appreciated!

    Thanks in advance
    Last edited by gastongr; 02-08-13 at 00:30.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,479
    This may be one of typical issues related with JOINs(equal or more than three tables) and GROUP BY.

    Please see the result of before grouping, like ...
    Code:
    SELECT *
     FROM  users                AS u
     LEFT  JOIN
           payment_transactions AS t
      ON   t.trx_user_id   = u.user_id
     LEFT  JOIN
           orders               AS o
      ON   o.order_user_id = u.user_id
    ;

    Try Grouping and SUMs before JOIN.

    Example 1:
    Code:
    SELECT user_name
         , deposits
         , withdrawals
         , orders
     FROM
           users AS u
     LEFT  JOIN
           (SELECT trx_user_id
                 , SUM(CASE WHEN trx_amount > 0 THEN trx_amount ELSE 0 END) AS deposits
                 , SUM(CASE WHEN trx_amount < 0 THEN trx_amount ELSE 0 END) AS withdrawals
             FROM  payment_transactions
             GROUP BY 
    	        trx_user_id
           ) AS t
      ON   t.trx_user_id   = u.user_id
     LEFT  JOIN
           (SELECT order_user_id
                 , SUM(order_amount) AS orders
             FROM  orders
             GROUP BY
                   order_user_id
           ) AS o
      ON   o.order_user_id = u.user_id
    ;

  3. #3
    Join Date
    Jan 2013
    Posts
    13
    Please see the result of before grouping, like ...
    Thanks for the advice!

    Your example worked just as desired, so thanks for that too!

  4. #4
    Join Date
    Jan 2013
    Posts
    13
    Well now i have another problem.

    I only need to display the users that at least made a payment transaction or an order, using left join it will show all the users:

    Code:
    ------------+----------+--------------+--------+
    | user_name | Deposits | widthdrawals | orders |
    ------------+----------+--------------+--------*
    |   Jhon    |  100.00  |    -87.00    |  15.55 |
    |   Paul    |   00.00  |     00.00    |  00.00 |
    |   George  |   65.30  |     00.00    |  25.00 |
    |   Ringo   |   00.00  |     00.00    |  00.00 |
    ------------+----------+--------------+--------+
    I need:

    Code:
    ------------+----------+--------------+--------+
    | user_name | Deposits | widthdrawals | orders |
    ------------+----------+--------------+--------*
    |   Jhon    |  100.00  |    -87.00    |  15.55 |
    |   George  |   65.30  |     00.00    |  25.00 |
    ------------+----------+--------------+--------+
    Any help on this would be great!

  5. #5
    Join Date
    Jan 2013
    Posts
    13
    I need to make a full join between the PAYMENT_TRANSACTIONS table and ORDERS table and then a simple join with the USERS table but i am not having luck with the full join..
    I have read How to simulate FULL OUNTER JOIN in MySQL but none of my attempts worked

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,479
    Quote Originally Posted by gastongr View Post
    Well now i have another problem.

    I only need to display the users that at least made a payment transaction or an order, using left join it will show all the users:

    Code:
    ------------+----------+--------------+--------+
    | user_name | Deposits | widthdrawals | orders |
    ------------+----------+--------------+--------*
    |   Jhon    |  100.00  |    -87.00    |  15.55 |
    |   Paul    |   00.00  |     00.00    |  00.00 |
    |   George  |   65.30  |     00.00    |  25.00 |
    |   Ringo   |   00.00  |     00.00    |  00.00 |
    ------------+----------+--------------+--------+
    I need:

    Code:
    ------------+----------+--------------+--------+
    | user_name | Deposits | widthdrawals | orders |
    ------------+----------+--------------+--------*
    |   Jhon    |  100.00  |    -87.00    |  15.55 |
    |   George  |   65.30  |     00.00    |  25.00 |
    ------------+----------+--------------+--------+
    Any help on this would be great!
    From where came the orders 15.55 for Jhon(user_id = 1) and 25.00 for George(user_id = 3)?

    Orders were
    Code:
    ORDERS
    ----------------+--------------+
    | order_user_id | order_amount |
    ----------------+--------------+
    |       3       |     4.00     |
    |       3       |    10.00     |
    |       1       |    15.00     |
    ----------------+--------------+

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,479
    Quote Originally Posted by gastongr View Post
    ...

    Code:
    ------------+----------+--------------+--------+
    | user_name | Deposits | widthdrawals | orders |
    ------------+----------+--------------+--------*
    |   Jhon    |  100.00  |    -87.00    |  15.55 |
    |   Paul    |   00.00  |     00.00    |  00.00 |
    |   George  |   65.30  |     00.00    |  25.00 |
    |   Ringo   |   00.00  |     00.00    |  00.00 |
    ------------+----------+--------------+--------+
    If you could get the result by a query,
    please try to add
    Code:
     WHERE deposits    <> 0
       OR  withdrawals <> 0
       OR  orders      <> 0
    for the query
    to get the result
    Code:
    ------------+----------+--------------+--------+
    | user_name | Deposits | widthdrawals | orders |
    ------------+----------+--------------+--------*
    |   Jhon    |  100.00  |    -87.00    |  15.55 |
    |   George  |   65.30  |     00.00    |  25.00 |
    ------------+----------+--------------+--------+
    ...

  8. #8
    Join Date
    Jan 2013
    Posts
    13
    From where came the orders 15.55 for Jhon(user_id = 1) and 25.00 for George(user_id = 3)?
    Ops sorry, orders should be 15 for Jhon and 14 for George as you would expect..

    Thank you so much for the help limiting the result set, i was trying to simulate a full join and you solved it with a simple WHERE!
    Thanks again!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •