Solved

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

Posted on 2013-11-18
8
318 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 32

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 32

Expert Comment

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

 

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
 
LVL 32

Expert Comment

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

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 32

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

777 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