tsql - Check for equal amounts of negative numbers as positive numbers -

i have table 2 columns: intgroupid, decamount

i want have query can return intgroupid result if every positive(+) decamount, there equal , opposite negative(-) decamount.

so table of (id=1,amount=1.0),(1,2.0),(1,-1.0),(1,-2.0) return intgroupid of 1, because each positive number there exists negative number match.

what know far there must equal number of decamounts (so enforce count(*) % 2 = 0) , sum of rows must = 0.0. however, cases logic are:

id | amount

• 1 | 1.0
• 1 | -1.0
• 1 | 2.0
• 1 | -2.0
• 1 | 3.0
• 1 | 2.0
• 1 | -4.0
• 1 | -1.0

this has sum of 0.0 , has number of rows, there not 1-for-1 relationship of positives negatives. need query can tell me if there negative amount each positive amount, without reusing of rows.

i tried counting distinct absolute values of numbers , enforcing less count of rows, it's not catching everything.

the code have far:

    declare @tbltest table(     intgroupid int     ,decamount decimal(19,2) );  insert @tbltest (intgroupid ,decamount) values (1,-1.0),(1,1.0),(1,2.0),(1,-2.0),(1,3.0),(1,2.0),(1,-4.0),(1,-1.0);  declare @intabscount int = 0     ,@intfullcount int = 0;  select @intfullcount = count(*) @tbltest;  select @intabscount = count(*) ( select distinct abs(decamount) abscount @tbltest group abs(decamount) ) abscount  select t1.intgroupid @tbltest t1      /* make sure number of rows */     inner join     (select count(*) intcount @tbltest      )     t2 on t2.intcount % 2 = 0      /* make sure sum = 0.0 */     inner join     (select sum(decamount) decsum @tbltest)     t3 on decsum = 0.0  /* make sure count of absolute values < count of values */      @intabscount < @intfullcount group t1.intgroupid 

i think there better way check table, possibly finding pairs , removing them table , seeing if there's left in table once there no more positive/negative matches, i'd rather not have use recursion/cursors.

boy, found simpler way previous answers. hope crazy edits saved posterity.

• this works grouping numbers id absolute value (1, -1 grouped 1).
• the sum of group determines if there equal number of pairs. if 0 equal, other value sum means there imbalance.
• the detection of evenness count aggregate necessary detect number of zeros. assumed 0's exist , should occur number of times. remove if isn't concern, 0 pass first test.
• i rewrote query bunch of different ways best execution plan. final result below has 1 big heap sort unavoidable given lack of index.

query

with tt (     select intgroupid,          case when sum(decamount) > 0 or count(*) % 2 = 1 1 else 0 end unequal     @tbltest      group intgroupid, abs(decamount) ) select tt.intgroupid,      case when sum(unequal) != 0 'not equal' else 'equals' end [pair] tt group intgroupid; 

tested values

(1,-1.0),(1,1.0),(1,2),(1,-2), -- should work (2,-1.0),(2,1.0),(2,2),(2,2), -- fail, 2 positive twos (3,1.0),(3,1.0),(3,-1.0), -- fail 2 1's , 1 -1 (4,1),(4,2),(4,-.5),(4,-2.5), -- fail: adds same sum, different values (5,1),(5,-1),(5,0),(5,0), -- work, test zeros (6,1),(6,-1),(6,0), -- fail, test zeros (7,1),(7,-1),(7,-1),(7,1),(7,1) -- fail, 3 x 1 

results

a   pairs _   _____ 1   equal 2   not equal 3   not equal 4   not equal 5   equal 6   not equal 7   not equal