CODEWITHSUNDEEP
mysqlsql
Machine
Machine

Reputation: 31

How to add more conditions with MySQL query?

SELECT 
`loanac`.`id`, 
`loanac`.`name`, 
`loanac`.`lacc`, 
SUM(`loantrans`.`in`) as totalin, 
SUM(`loantrans`.`out`) as totalout

FROM loanac, loantrans
WHERE `loanac`.`lacc`=`loantrans`.`account`
GROUP BY `loanac`.`lacc`

Here is my query above which is working fine to show all accounts, BUT I need to find accounts where SUM(out) > SUM (in) instead of all accounts

I've tried to add condition using AND but its showing error, can anyone help?

Upvotes: 0

Views: 64

Answers (2)

aznatam
aznatam

Reputation: 204

Try using having

SELECT loanac.id, 
loanac.name, 
loanac.lacc, 
loanac.phone, 
SUM(loantrans.in) as totalin, 
SUM(loantrans.out) as totalout 
FROM loanac, loantrans 
WHERE loanac.lacc=loantrans.account 
GROUP BY loanac.lacc 
HAVING SUM(out) > SUM (in)

Upvotes: 1

Scott
Scott

Reputation: 3732

The way to do criteria on aggregate functions (like SUM or COUNT) is HAVING, not WHERE

SELECT loanac.id, loanac.name, loanac.lacc, loanac.phone, 
       SUM(loantrans.in) as totalin, SUM(loantrans.out) as totalout 
FROM loanac, 
     loantrans 
WHERE loanac.lacc=loantrans.account 
GROUP BY loanac.lacc
HAVING SUM(loantrans.out) > SUM(loantrans.in)

Upvotes: 4

Related Questions