Question 113634 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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  |  [email protected]  | 15.50   
2  |  [email protected]  | 10.00   
3  |  [email protected]  | 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
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0 
    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


  2. 0

    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_id email balance total_deposits spent
    1 [email protected] 15.50 20.00 21.00
    2 [email protected] 10.00 24.00 25.50
    3 [email protected] 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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search