Using a function in a where-clause

Question

I have a MySQL-Database where I run certain Stored Procedures.

In one of them, I want to sum up the amount a certain user has to pay, the amount he already payed and return all the users, where the amount to be payed isn't equal the amount paid. I came up with the following (simplyfied) query:

SELECT userId, SUM(costs) as sum_costs, 
  (SELECT SUM(payed)
   FROM payments p WHERE ta.userId=p.userId) as sum_payed
FROM ta 
GROUP BY ta.userId
ORDER BY ta.userId;

This gives me the sum of the costs and the payment for each user. But I do not know, how I can select only the users, where costs and payment are not equal.

WHERE sum_costs != sum_payed

doesn't work, because mysql doesn't know the column 'sum_costs' in the WHERE-clause.

Any idea, how to get the selection to work?

a1ex07 · Accepted Answer · 2012-12-12 19:24:50Z

1

SELECT * FROM 
(
SELECT userId, SUM(costs) as sum_costs, 
(SELECT SUM(payed)
FROM payments p WHERE ta.userId=p.userId) as sum_payed
FROM ta 
GROUP BY ta.userId
)a
WHERE a.sum_costs <> a.sum_payed 
--ORDER BY a.userId;

Update: actually no need for ORDER BY UserId since it will be ordered implicitly by GROUP BY

answered Dec 12, 2012 at 19:24

a1ex07

37.4k12 gold badges94 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

valex · Accepted Answer · 2012-12-12 19:47:36Z

1

SELECT * from
(select userId, SUM(costs) as sum_costs
    FROM ta 
       GROUP BY ta.userId) t1
 left join
  (SELECT userId,SUM(payed) as sum_payed FROM payments p group by userId) t2
   on t1.UserId=t2.UserId

where t1.sum_costs<>t2.sum_payed
ORDER BY t1.userId;

answered Dec 12, 2012 at 19:47

valex

24.2k7 gold badges47 silver badges62 bronze badges

Comments

Russell Fox · Accepted Answer · 2012-12-12 19:24:33Z

0

WHERE SUM(costs) <> (SELECT SUM(payed) FROM payments p WHERE ta.userId=p.userId)

answered Dec 12, 2012 at 19:24

Russell Fox

5,4651 gold badge26 silver badges28 bronze badges

Collectives™ on Stack Overflow

Using a function in a where-clause

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related