Issue

This Content is from Stack Overflow. Question asked by Jozeph OFATTOLE

how to join 3 tables to get ( SUM orders and SUM (transactions – fees)) per user ?

tables structure :
I query bellow, but unfortunately it returns the wrong total of sum orders. What I want is to know all users whose total orders exceed the sum of their diposit.

``````USERS

-----------------------------------
id |  email           | balance
-----------------------------------
1  |  email1@app.com  | 15.50
2  |  email2@app.com  | 10.00
3  |  email3@app.com  | 70.00
-----------------------------------

TRANSACTIONS

-----------------------------------
id | user_id | amount | fees
-----------------------------------
1  |  1      | 15.50  | 0.50
2  |  2      | 10.00  | 0.50
3  |  2      | 15.00  | 0.50
4  |  3      | 12.50  | 0.50
5  |  1      | 5.50   | 0.50
-----------------------------------

ORDERS

-----------------------------------
id | user_id | charge
-----------------------------------
1  |  1      | 15.50
2  |  2      | 10.00
3  |  2      | 15.00
4  |  3      | 12.50
5  |  1      | 5.50
-----------------------------------

the expected result
----------------------------------------------------------------------
id |  email           | balance | sum(diposits)   |  sum(transaction)
----------------------------------------------------------------------
1  |  email1@app.com  | 15.50   | 250             |  265.50
2  |  email2@app.com  | 10.00   | 50.50           |  60.50
3  |  email3@app.com  | 70.00   | 650.50          |  720.50
----------------------------------------------------------------------

SELECT u.id, u.email,u.balance,sum(t.amount),sum(o.charge)
FROM general_users u
INNER JOIN general_transaction_logs t ON u.id = t.uid
INNER JOIN orders o ON u.id = o.uid
GROUP BY u.id;

Thank you
``````

Solution

You can achieve this multiple ways:

1. Using a `subquery`:
``````  SELECT a.id AS user_id,
a.email,
a.balance,
b.total_deposits,
c.spent
FROM
USERS a
INNER JOIN
(SELECT t.uid,
SUM(t.amount - t.fees) AS total_deposits
FROM TRANSACTIONS t
GROUP BY t.uid) b
ON a.id = b.uid
INNER JOIN
(SELECT o.uid,
SUM(o.charge) AS spent
FROM ORDERS o
GROUP BY o.uid) c
ON a.id = c.uid
WHERE b.total_deposits < c.spent
``````
1. Using a Common Table Expression (`CTE`):
``````   WITH b AS
(SELECT t.uid,
SUM(t.amount - t.fees) AS total_deposits
FROM TRANSACTIONS t
GROUP BY t.uid),
c AS
(SELECT o.uid,
SUM(o.charge) AS spent
FROM ORDERS o
GROUP BY o.uid)
SELECT a.id AS user_id,
a.email,
a.balance,
b.total_deposits,
c.spent
FROM
USERS a
INNER JOIN b ON a.id = b.uid
INNER JOIN c ON a.id = c.uid
WHERE b.total_deposits < c.spent
``````

Result:

user_idemailbalancetotal_depositsspent
1email1@app.com15.5020.0021.00
2email2@app.com10.0024.0025.50
3email3@app.com70.0012.0012.50

Fiddle here.

The reason for this is you cannot use a column alias in your `WHERE` clause, the MySQL Documentation states this:

Standard SQL disallows references to column aliases in a WHERE clause.
This restriction is imposed because when the WHERE clause is
evaluated, the column value may not yet have been determined. For
example, the following query is illegal:

``````SELECT id, COUNT(*) AS cnt FROM tbl_name
WHERE cnt > 0 GROUP BY id;
``````

You can also read more about it in the following previous questions:

Final Note: your `GROUP BY` was incorrect, it contained the `id` column but not the `email` and `balance` column which will throw an aggregation error. To include the `email` and `balance` without adding it to your `GROUP BY`, you can use a `JOIN` as I did in my examples above.

UPDATE: I’ve added the column `uid` to the `JOIN`‘s above. The column `id` was the only provided id column in your sample data. I’ve also updated my Queries, Result, and Fiddle to reflect your Answer above which shows you wanted the `SUM`‘s to occur outside of the `JOIN`‘s.

``` This Question was asked in  StackOverflow by  Jozeph OFATTOLE and Answered by GRIV It is licensed under the terms of
CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.```