Solved

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

Posted on 2013-11-18
8
320 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 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Syntax to get a value associated with most recent date 16 64
restriction of entering a a page 5 44
Estimating my database size 7 51
Mysql query one to many 11 40
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…

739 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