• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 328
  • Last Modified:

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.

Select Distinct
  ledger.accountid,
  Round(Sum(ledger.amount), 2) As BalanceDue
From
  ledger
Where
  ledger.glaccountid = 1
Group By
  ledger.accountid

Open in new window

0
ckelsoe
Asked:
ckelsoe
  • 5
  • 3
1 Solution
 
awking00Commented:
Add this -
having Round(Sum(ledger.amount), 2) > 0
0
 
ckelsoeAuthor Commented:
Ah - forgot about having. I did <>0 and it is what I need. Thanks
0
 
awking00Commented:
Assuming you could have negative sums, just change to -
having Round(Sum(ledger.amount), 2) <> 0
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
ckelsoeAuthor Commented:
Can you do an and in having? When I try I get no results

For example,
having (Round(Sum(ledger.amount), 2) < -1
and Round(Sum(ledger.amount), 2) > 1)
0
 
awking00Commented:
Sorry, I didn't see your last post where you already figured it out :-)
0
 
awking00Commented:
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), 2) < -1
OR Round(Sum(ledger.amount), 2) > 1)
0
 
ckelsoeAuthor Commented:
Yes - that is what I needed.  Thanks and have a great rest of the day!
0
 
awking00Commented:
Thanks, you do the same. Not sure mine will be that great, I have a dentist appointment :-(
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now