Multiple JOINS cause wrong sum

gastongr · #1 (**permalink**) 02-08-13, 00:26

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

tonkuma · #2 (**permalink**) 02-09-13, 10:51

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
;

gastongr · #3 (**permalink**) 02-09-13, 17:21

Quote:

Please see the result of before grouping, like ...

Thanks for the advice!

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

gastongr · #4 (**permalink**) 02-13-13, 13: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!

gastongr · #5 (**permalink**) 02-13-13, 13:21

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

tonkuma · #6 (**permalink**) 02-13-13, 15:11

Quote:

Originally Posted by gastongr

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     |
----------------+--------------+

tonkuma · #7 (**permalink**) 02-13-13, 15:29

Quote:

Originally Posted by gastongr

...

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

Quote:

Code:

------------+----------+--------------+--------+
| user_name | Deposits | widthdrawals | orders |
------------+----------+--------------+--------*
|   Jhon    |  100.00  |    -87.00    |  15.55 |
|   George  |   65.30  |     00.00    |  25.00 |
------------+----------+--------------+--------+

...

gastongr · #8 (**permalink**) 02-14-13, 11:24

Quote:

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!

Internet Services

Web Development

Digital Marketing

Consumer Tech