How to add more conditions with MySQL query?

Question

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?

possible duplicate of [WHERE vs HAVING](http://stackoverflow.com/questions/2905292/where-vs-having) — M Khalid Junaid, Aug 21 '14 at 17:01

score 4 · Accepted Answer · answered Aug 21 '14 at 16:44

4

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)

answered Aug 21 '14 at 16:44

Scott

3,663
8
33
56

Glad you tried to help, your query returning only 3 results but there should be more than 300 results! Any idea how to solve now? – Machine Aug 21 '14 at 19:03
Try grouping by all non-aggregate columns – Scott Aug 21 '14 at 19:04
From my view, I can only group by loanac.lacc or loantrans.account , but both returning same result :( – Machine Aug 21 '14 at 19:11
Without having access to your data, I can't answer why you're getting a different number of records than you expect. In SQL you must group by any non-aggregate column, and the fact that you aren't is confusing to me. Nevertheless, using the Having clause is correct. – Scott Aug 21 '14 at 19:15
Scott, here is the db uploaded for you, will you please take a look? https://www.dropbox.com/sh/o0ro7r23ztnpfky/AABrfLYUB5DUk-FRvPegHZF_a – Machine Aug 21 '14 at 19:27
@Machine, the answer to "some number > Null" is false. Replace NULL in the loantrans.in and loantrans.out data with 0 to get the results you want. Alternatively, use a function to replace null with 0 in your query. Only accounts 440, 974, and 952 have non-null deposit totals. – Scott Aug 21 '14 at 20:55
Bro I'm messed up with "BETWEEN" and "NOT BETWEEN", Will you please help? http://stackoverflow.com/questions/26026668/how-to-use-opposite-of-between-in-a-mysql-query?noredirect=1#comment40768728_26026668 – Machine Sep 24 '14 at 23:09
I'm still stuck :( . SCREENSHOT: https://www.dropbox.com/sh/lgmop3zxqaf9mjy/AAAWVWjZXd8da9KDDOqz2ANla?dl=0 – Machine Sep 27 '14 at 09:04

score 1 · Answer 2 · edited Aug 21 '14 at 16:58

1

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)

edited Aug 21 '14 at 16:58

M Khalid Junaid

63,861
10
90
118

answered Aug 21 '14 at 16:44

aznatam

204
1
4

A little description and proper formatting your answer can bring a good reputation but without this you will miss these points – M Khalid Junaid Aug 21 '14 at 17:00
Glad you tried to help, your query returning only 3 results but there should be more than 300 results! Any idea how to solve now? – Machine Aug 21 '14 at 19:03

How to add more conditions with MySQL query?

2 Answers2