Solved

Query to return sum of values where the sum <> 0

Posted on 2013-11-18
8
316 Views
Last Modified: 2013-11-18
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
Comment
Question by:ckelsoe
  • 5
  • 3
8 Comments
 
LVL 31

Accepted Solution

by:
awking00 earned 500 total points
ID: 39657377
Add this -
having Round(Sum(ledger.amount), 2) > 0
0
 

Author Closing Comment

by:ckelsoe
ID: 39657384
Ah - forgot about having. I did <>0 and it is what I need. Thanks
0
 
LVL 31

Expert Comment

by:awking00
ID: 39657398
Assuming you could have negative sums, just change to -
having Round(Sum(ledger.amount), 2) <> 0
0
 

Author Comment

by:ckelsoe
ID: 39657402
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 31

Expert Comment

by:awking00
ID: 39657405
Sorry, I didn't see your last post where you already figured it out :-)
0
 
LVL 31

Expert Comment

by:awking00
ID: 39657413
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
 

Author Comment

by:ckelsoe
ID: 39657416
Yes - that is what I needed.  Thanks and have a great rest of the day!
0
 
LVL 31

Expert Comment

by:awking00
ID: 39657434
Thanks, you do the same. Not sure mine will be that great, I have a dentist appointment :-(
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now