asked on Query to return sum of values where the sum <> 0
I need to return the sum of fields grouped by accountid. I only want to see the results where the resulting sum is not equal to 0.
The query below works except I get a bunch of rows with a value of 0 which I do not want. What am I missing? Basic SQL but I am stuck.
The query below works except I get a bunch of rows with a value of 0 which I do not want. What am I missing? Basic SQL but I am stuck.
Select Distinct
ledger.accountid,
Round(Sum(ledger.amount), 2) As BalanceDue
From
ledger
Where
ledger.glaccountid = 1
Group By
ledger.accountid
MySQL ServerSQL
Last Comment
awking00
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Ah - forgot about having. I did <>0 and it is what I need. Thanks
Assuming you could have negative sums, just change to -
having Round(Sum(ledger.amount), 2) <> 0
having Round(Sum(ledger.amount), 2) <> 0
ASKER
Can you do an and in having? When I try I get no results
For example,
having (Round(Sum(ledger.amount),
and Round(Sum(ledger.amount), 2) > 1)
For example,
having (Round(Sum(ledger.amount),
and Round(Sum(ledger.amount), 2) > 1)
Sorry, I didn't see your last post where you already figured it out :-)
Can you do an and in having? When I try I get no results
No (a sum can't be both plus and minus), but you could do OR
having (Round(Sum(ledger.amount),
OR Round(Sum(ledger.amount), 2) > 1)
No (a sum can't be both plus and minus), but you could do OR
having (Round(Sum(ledger.amount),
OR Round(Sum(ledger.amount), 2) > 1)
ASKER
Yes - that is what I needed. Thanks and have a great rest of the day!
Thanks, you do the same. Not sure mine will be that great, I have a dentist appointment :-(