How to remove rows when the sum of two values equals zero-CodePudding

I'm struggling on how can I remove values from a column that are different only on the sign. For example:


 --------- ---------- ---------- 
| Company | Total    | ID       |
 --------- ---------- ---------- 
| BFG     |       -13|         1|
| AAA     |        11|         2|
| CCC     |         5|         3|
| BFG     |        13|         4|
| AAA     |       -11|         5|
| AAA     |       -11|         6|
| CCC     |         7|         7|
| CCC     |        -7|         8|
 --------- ---------- ---------- 


-- Desired result, as something like
 --------- ---------- ---------- 
| Company | Debt     | ID       |
 --------- ---------- ---------- 
| CCC     |         5|         3|
| AAA     |       -11|         6|
 --------- ---------- ----------

Note that the order of the removing don't matter (case of ID 2, 5 and 6).

Any hint on how can I do that?

CodePudding user response：

You can pair positive numbers with negative ones, one by one. Then remove all the matches.

For example:

with
p as (select *, row_number() over(partition by total order by id) as rn from t where total > 0),
n as (select *, row_number() over(partition by total order by id) as rn from t where total < 0),
m as (
  select p.id as pid, n.id as nid
  from p
  join n on -n.total = p.total and n.rn = p.rn
)
delete from t
where id in (select pid from m)
   or id in (select nid from m)

Result:

 company  total  id 
 -------- ------ -- 
 CCC      5      3  
 AAA      -11    6

See running example at db<>fiddle.

CodePudding user response：

After considering what @Jeroen Mostert mentioned in the comments I had another bash at finding a solution using a windowed partition, does the following work for you?

with Id as (
    select Max(Id) Id from (
      select *, Abs(Sum(total) over(partition by company, Abs(total))) tot
      from t
    )t
    where tot > 0
    group by company
)
select *
from id join t on Id.id = t.id

CodePudding user response：

Does this work?

Declare @testTable table (Company varchar(10), Total int, ID int)
 Insert Into @testTable (Company, Total, ID)
 Values ('BFG', -13, 1)
      , ('AAA',  11, 2)
      , ('CCC',   5, 3)
      , ('BFG',  13, 4)
      , ('AAA', -11, 5)
      , ('AAA', -11, 6)
      , ('CCC',   7, 7)
      , ('CCC',  -7, 8)
      , ('DDD', -10, 9)
      , ('DDD',  10, 10)
      , ('DDD',  10, 11);

   With groupedRows
     As (
 Select *
      , rn = row_number() Over(Partition By tt.Company, tt.Total Order By tt.ID)
   From @testTable tt
        )
 Select P.Company
      , P.Total
      , P.ID
   From groupedRows             p
   Left Join groupedRows        n On n.rn = p.rn And -n.Total = p.Total
  Where n.rn Is Null;