Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-11-18
8
Medium Priority
?
322 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 32

Accepted Solution

by:
awking00 earned 2000 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

715 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