how to select all users whose total orders exceed the sum of their diposit?

Question

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

SELECT u.id, u.email,u.balance,sum(t.amount - t.fees) as total_disposits ,sum(o.charge) as spent
    FROM USERS u
    INNER JOIN TRANSACTIONS t ON u.id = t.uid
    INNER JOIN ORDERS o ON u.id = o.uid
WHERE total_disposits < spent
    GROUP BY u.id;

tables structure :

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

Thank you

You can use output columns in WHERE never. Your output columns expressions uses aggregate functions - you may/must test them in HAVING. — Akina, Sep 19 '22 at 05:14
*tables structure* They'd be provided as CREATE TABLE + INSERT INTO. — Akina, Sep 19 '22 at 05:15

griv · Answer 1 · 2022-09-20T13:21:03.450

You can achieve this multiple ways:

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

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_id	email	balance	total_deposits	spent
1	email1@app.com	15.50	20.00	21.00
2	email2@app.com	10.00	24.00	25.50
3	email3@app.com	70.00	12.00	12.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.

both of the queries doesnt return anything ,(big thanks for the final note) — Jozeph OFATTOLE, Sep 20 '22 at 04:48
@JozephOFATTOLE I've updated my answer, you seem to have a column you're using for your `JOINS` called `uid`, I've added it to my answer, I was using the `id` column that you provided in your sample data above. — griv, Sep 20 '22 at 12:55
@JozephOFATTOLE I've updated my answer once again to reflect your Answer above using the `SUM`'s outside of the `JOIN`'s. My first query is similar to yours except I'm `SELECT`ing from the user table directly, there's technically no need to put it in a subquery like you did. Also, check out the `CTE` query. Hope that helps. — griv, Sep 20 '22 at 13:22

Jozeph OFATTOLE · Answer 2 · 2022-09-20T07:46:00.227

SELECT user_id, u.balance, total_deposits, spent 
FROM (SELECT id AS user_id ,balance FROM USERS ) u 
JOIN ( SELECT SUM(charge) AS spent, uid FROM ORDERS  GROUP BY uid) o 
    ON u.user_id = o.uid 
JOIN ( SELECT SUM(amount) - SUM(fees) AS total_deposits, uid FROM TRANSACTIONS GROUP BY uid) t 
    ON u.user_id = t.uid 
WHERE spent > total_deposits

the query inspired from the answer : https://stackoverflow.com/a/71702680/3641989

IMPORTANT : There are no relationships between these tables

how to select all users whose total orders exceed the sum of their diposit?

2 Answers2