If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > MySQL > Multiple JOINS cause wrong sum

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 11
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.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Feb 2008
Location: Japan
Posts: 3,386
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
;
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 11
Quote:
Please see the result of before grouping, like ...
Thanks for the advice!

Your example worked just as desired, so thanks for that too!
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 11
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!
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 11
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
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Feb 2008
Location: Japan
Posts: 3,386
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     |
----------------+--------------+
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Feb 2008
Location: Japan
Posts: 3,386
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
Quote:
Code:
------------+----------+--------------+--------+
| user_name | Deposits | widthdrawals | orders |
------------+----------+--------------+--------*
|   Jhon    |  100.00  |    -87.00    |  15.55 |
|   George  |   65.30  |     00.00    |  25.00 |
------------+----------+--------------+--------+
...
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 11
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!
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On