CODEWITHSUNDEEP
sqlsqlcommand
Attp30998
Attp30998

Reputation: 5

SQL command if sum = 0 by criteria

In my table I have Voucher, Dimension and Amount. Each Voucher is unique, and Dimension is usually similar per two Vouchers. There is also usually an identical positive and negative amount per Voucher for each Dimension. I'm looking for a command that will exclude every Dimension that is summarised to 0.

So far I have

Select Dimension,
       Sum(Amount)
From "table"
Group by Dimension;

This gives me a table where I can see the summarised amount per Dimension.

How to expand this to give me Voucher with Dimension that doesn't summarize to 0?

The actual table contains 200 000 rows, but I have made an example to illustrate what I'm starting with and what I hope to get as a result:

[Table]

1

[Result]

2

Upvotes: 0

Views: 80

Answers (2)

Yousuf
Yousuf

Reputation: 1

You can try something like this:

Select a.voucher, b.dimension from table a

left join(Select dimension, sum(amount) 
from table 
group by 1
having sum(amount) <> 0) b on a.dimension = b.dimension;

Upvotes: 0

demo
demo

Reputation: 6245

It can be like this

To filter rows only with SUM equal 0

Select Dimension
From "table"
Group by Dimension
Having Sum(Amount) = 0

And make above query as subquery to exclude Dimensions with SUM equal 0

SELECT *
FROM "table"
WHERE Dimension NOT IN (
    Select Dimension
    From "table"
    Group by Dimension
    Having Sum(Amount) = 0
)

It will return you full records from "table".

At least it should work for MS SQL DBMS.

Upvotes: 1

Related Questions