CODEWITHSUNDEEP
sqlselectsumfirebird
user1455790
user1455790

Reputation: 11

How to return only non-zero values from a Select sum statement

I have a query returning 2 columns and multiple rows. How do I only return a sum value which is not 0 or null?

Table "audit" is like this:

parent_link   integer;
dr            smallint
amount        decimal(18,2)

select parent_link, sum(case dr when 1 then amount else -amount end)
from audit where books = 3 group by parent_link

I am checking to see if the values placed into the amount column balance for 1 and 0 values in the dr column.

This query works and returns a few thousand rows with 0 as the sum result and 2 with a value in the sum. I only want to return the rows with a value for the sum.

Any help would be much appreciated.

Upvotes: 1

Views: 29065

Answers (2)

Sergei Danielian
Sergei Danielian

Reputation: 5025

Also, try a query like this: (it doesn't require any additional calculations)

SELECT _inner._SUM FROM
(
    SELECT parent_link, SUM(CASE dr WHEN 1 THEN amount ELSE -amount END) AS _SUM
      FROM audit WHERE books = 3 GROUP BY parent_link
) AS _inner
WHERE _inner._SUM <> 0

Upvotes: 2

Guffa
Guffa

Reputation: 700630

Use having:

select parent_link, sum(case dr when 1 then amount else -amount end)
from audit
where books = 3
group by parent_link
having sum(case dr when 1 then amount else -amount end) <> 0

Upvotes: 13

Related Questions