Remove rows that has a sum of 0

[penandpaper]

Hello,

Is it possible to remove rows that has a sum of 0?

Table:

And my expected result should be:

I don't have any idea if this is possible because the subledger is all the same thus I don't know how to use the "group by". Any help would be appreciated.
Thanks.

 

[plog]

Sum implies addition. I don't see where you did any addition. From a logical standpoint it just looks like you kicked out the highest and lowest values.

Please explain how SUM plays into this.

 

[penandpaper]

Well the values here in the result image are equal to 0 if u sum it, thus the -25755 and 48994 should not be in the result.

Basically I'm looking for values that has a sum equal to 0

 

[plog]

That makes no sense. How are you getting to 0 from the data you are providing (table.jpg)?

The only difference between your table.jpg and your result.jpg is that the records with ActualAmount=-25775 and ActualAmount=48994 are omitted. Please explain how you logically get from table.jpg to result.jpg with the information you have given us.

 

[penandpaper]

Well the expected result should be the result image but the problem is when I do the query the output is that it selects all of the values just like in the table image.

 

[plog]

By virtue of you posting for help, I figured it didn't work in some manner. Now we are working on the nuts and bolts. You can't just gloss over my question--to get this to work you must specify the logic involved.

Perhaps you can provide more sample data?

 

[Minty]

@Plog Some how, it appears the OP wants to randomly add up all possible values with the same Subledger code until they equal 0 and display those values.

I've no idea how you would work out what to do if there was more than one permutation though. Maybe the world stops turning at that point?

 

[penandpaper]

Sorry for the lack of data, basically the table is like this:

From the table the those in subledger that is WWE12 and AAA333 has a sum of 0 when added together. But in WWE12 it has 5 data where in it is -217, -43, -260, and 48994. If you add -217, -43, -260 it has an equal value of 0. And the result I want is rows that has a value of 0, But the problem is that in WWE12 there is a data that is 48994 which should not be in the result expected.

My Code is like this:

SELECT Subledger, ActualAmount
FROM tblOne
WHERE Subledger IN (
SELECT Subledger FROM tblOne
GROUP BY Subledger
HAVING SUM(ActualAmount) = 0
)

I know the problem is with my Group by Clause and I don't have any idea how to filter it more so it just returns the desired output.

 

[plog]

I see the logic now, but its not going to be easy to achieve. I mean consider this data:

Subledger, ActualAmount
AAA, -1
AAA, -2
AAA, -4
AAA, 1
AAA, 2
AAA, 3
AAA, 4
AAA, 5
AAA, 6
AAA, 7

From that sample data you would expect no results. Correct?

 

[penandpaper]

From your example we can remove the sum of the negative w/c is -7 and 7.

 

[plog]

From your example we can remove the sum of the negative w/c is -7 and 7.

Huh?

Using my dataset. What records would you expect returned by this query we are working on?

 

[penandpaper]

Ooops sorry it should be:

Subledger, ActualAmount
AAA, -1
AAA, -2
AAA, -4
AAA, 7

 

[plog]

What about all the others? Their sums can also be made into zero. For example:

AAA, -1
AAA, -2
AAA, 3

 

[penandpaper]

Yes but as long as the sum value is 0 it is okay. So basically it can remove anything.

 

[plog]

Again. Using my sample data. What results would you expect to see in this query we are building?

 

[penandpaper]

First of all I want to thank you for your response, I greatly appreciate it.

Any that has value that when sumed the answer is 0 but the query should be 1 to many or many to 1 Like:

Subledger, ActualAmount
AAA, -1
AAA, -2
AAA, 3

Or

Subledger, ActualAmount
AAA, -1
AAA, -4
AAA, 5

Or

Subledger, ActualAmount
AAA, -2
AAA, -4
AAA, 6

Or

Subledger, ActualAmount
AAA, -1
AAA, -2
AAA, -4
AAA, 7

It can remove any of the following above. Since it only need to remove rows that the sum is 0.

 

[plog]

I really don't know how to help you. You have a starting dataset, but you can't definitively say what should be returned.

 

[penandpaper]

It just need to return data that the sum is equals to 0..

 

[Minty]

But what happens when there is more than one possible answer?

This is what you have to make a rule for, without that any code the cleverest programmer in the world could write, can't guess at your preferred logic.

 

[penandpaper]

Im so sorry I know now what you mean and it's a mistake on my part so stressed with this.. Basically there's a condition and that is the sum of duplicate recrods of ColumnA and ColumnB that the sum is 0.

Basically:

ColumnA, ColumnB, Amount
123, 555, 10
234, 555, -25
234, 555, 10
234, 555, 15
123, 555, 20

Here is the result of my query that I've shown before. But I want to remove the values in ColumnA that has a value of 123.

Here is the code again:

SELECT * FROM table
WHERE ColumnA In
(
SELECT ColumnA FROM table
GROUP BY ColumnA, ColumnB
HAVING SUM(Amount)
)

I know the problem is with my group by, but I don't have any idea on this? please Help