Issue

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

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

2. 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:

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:

• Using column alias in WHERE clause of MySQL query produces an
  error
• WHERE vs HAVING

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.