CODEWITHSUNDEEP
mysqlsqlstored-procedures
Aaginor
Aaginor

Reputation: 4782

Using a function in a where-clause

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?

Upvotes: 1

Views: 117

Answers (3)

valex
valex

Reputation: 24144

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;

Upvotes: 1

a1ex07
a1ex07

Reputation: 37354

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

Upvotes: 1

Russell Fox
Russell Fox

Reputation: 5435

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

Upvotes: 0

Related Questions