Check for equal amounts of negative numbers as positive numbers

Question

I have a table with two columns: intGroupID, decAmount

I want to have a query that can basically return the intGroupID as a result if for every positive(+) decAmount, there is an equal and opposite negative(-) decAmount.

So a table of (id=1,amount=1.0),(1,2.0),(1,-1.0),(1,-2.0) would return back the intGroupID of 1, because for each positive number there exists a negative number to match.

What I know so far is that there must be an equal number of decAmounts (so I enforce a count(*) % 2 = 0) and the sum of all rows must = 0.0. However, some cases that get by that 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 a sum of 0.0 and has an even number of rows, but there is not a 1-for-1 relationship of positives to negatives. I need a query that can basically tell me if there is a negative amount for each positive amount, without reusing any of the rows.

I tried counting the distinct absolute values of the numbers and enforcing that it is less than the count of all rows, but it's not catching everything.

The code I have so far:

    DECLARE @tblTest TABLE(
    intGroupID INT
    ,decAmount DECIMAL(19,2)
);

INSERT INTO @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(*) FROM @tblTest;

SELECT @intABSCount = COUNT(*) FROM (
SELECT DISTINCT ABS(decAmount) AS absCount FROM @tblTest GROUP BY ABS(decAmount)
) AS absCount

SELECT t1.intGroupID
FROM @tblTest AS t1

    /* Make Sure Even Number Of Rows */
    INNER JOIN
    (SELECT COUNT(*) AS intCount FROM @tblTest 
    )
    AS t2 ON t2.intCount % 2 = 0

    /* Make Sure Sum = 0.0 */
    INNER JOIN
    (SELECT SUM(decAmount) AS decSum FROM @tblTest)
    AS t3 ON decSum = 0.0

/* Make Sure Count of Absolute Values < Count of Values */
WHERE 
    @intABSCount < @intFullCount
GROUP BY t1.intGroupID

I think there is probably a better way to check this table, possibly by finding pairs and removing them from the table and seeing if there's anything left in the table once there are no more positive/negative matches, but I'd rather not have to use recursion/cursors.

Answer 1

Boy, I found a simpler way to do this than my previous answers. I hope all my crazy edits are saved for posterity.

• This works by grouping all numbers for an id by their absolute value (1, -1 grouped by 1).
• The sum of the group determines if there are an equal number of pairs. If it is 0 then it is equal, any other value for the sum means there is an imbalance.
• The detection of evenness by the COUNT aggregate is only necessary to detect an even number of zeros. I assumed that 0's could exist and they should occur an even number of times. Remove it if this isn't a concern, as 0 will always pass the first test.
• I rewrote the query a bunch of different ways to get the best execution plan. The final result below only has one big heap sort which was unavoidable given the lack of an index.

Query

WITH tt AS (
    SELECT intGroupID, 
        CASE WHEN SUM(decAmount) > 0 OR COUNT(*) % 2 = 1 THEN 1 ELSE 0 END unequal
    FROM @tblTest 
    GROUP BY intGroupID, ABS(decAmount)
)
SELECT tt.intGroupID, 
    CASE WHEN SUM(unequal) != 0 THEN 'not equal' ELSE 'equals' END [pair]
FROM tt
GROUP BY 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, two positive twos
(3,1.0),(3,1.0),(3,-1.0), -- fail two 1's , one -1
(4,1),(4,2),(4,-.5),(4,-2.5), -- fail: adds up the same sum, but 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

Answer 2

You can compare your values this way:

declare @t table(id int, amount decimal(4,1))
insert @t 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),(2,-1.0),(2,1.0)

;with a as
(
select count(*) cnt, id, amount
from @t
group by id, amount
)
select id from @t
except
select b.id from a
full join a b
on a.cnt = b.cnt and a.amount = -b.amount
where a.id is null

For some reason i can't write comments, however Daniels comment is not correct, and my solution does accept (6,1),(6,-1),(6,0) which can be correct. 0 is not specified in the question and since it is a 0 value it can be handled eather way. My answer does NOT accept (3,1.0),(3,1.0),(3,-1.0)

To Blam: No I am not missing

or b.id is null

My solution is like yours, but not exactly identical

Answer 3

Does this help?

-- Expected result - group 1 and 3 
declare @matches table (groupid int, value decimal(5,2))
insert into @matches select 1, 1.0
insert into @matches select 1, -1.0
insert into @matches select 2, 2.0
insert into @matches select 2, -2.0
insert into @matches select 2, -2.0
insert into @matches select 3, 3.0
insert into @matches select 3, 3.5
insert into @matches select 3, -3.0
insert into @matches select 3, -3.5
insert into @matches select 4, 4.0
insert into @matches select 4, 4.0
insert into @matches select 4, -4.0


-- Get groups where we have matching positive/negatives, with the same number of each
select  mat.groupid, min(case when pos.PositiveCount = neg.NegativeCount then 1 else 0 end) as 'Match'
from    @matches mat
LEFT JOIN (select groupid, SUM(1) as 'PositiveCount', Value
                from @matches where value > 0 group by groupid, value) pos 
                on pos.groupid = mat.groupid and pos.value = ABS(mat.value)

LEFT JOIN (select groupid, SUM(1) as 'NegativeCount', Value
                from @matches where value < 0 group by groupid, value) neg 
                on neg.groupid = mat.groupid and neg.value = case when mat.value < 0 then mat.value else mat.value * -1 end

group by mat.groupid
-- If at least one pair within a group don't match, reject
having min(case when pos.PositiveCount = neg.NegativeCount then 1 else 0 end) = 1

Answer 4

Not tested but I think you can get the idea

This returns the id that do not conform
The not is easier to test / debug

select pos.*, neg.* 
  from 
     (  select id, amount, count(*) as ccount
          from tbl 
         where amount > 0 
         group by id, amount ) pos
  full outer join 
     (  select id, amount, count(*) as ccount
          from tbl 
         where amount < 0 
         group by id, amount ) neg
    on pos.id = neg.id 
   and pos.amount = -neg.amount 
   and pos.ccount = neg.ccount
 where pos.id is null 
    or neg.id is null 

I think this will return a list of id that do conform 

select distinct(id) from tbl 
except
select distinct(isnull(pos.id, neg.id)) 
  from 
     (  select id, amount, count(*) as ccount
          from tbl 
         where amount > 0 
         group by id, amount ) pos
  full outer join 
     (  select id, amount, count(*) as ccount
          from tbl 
         where amount < 0 
         group by id, amount ) neg
    on pos.id = neg.id 
   and pos.amount = -neg.amount 
   and pos.ccount = neg.ccount
 where pos.id is null 
    or neg.id is null

Answer 5

The following should return "disbalanced" groups:

;with pos as (
    select intGroupID, ABS(decAmount) m
    from TableName
    where decAmount > 0
), neg as (
    select intGroupID, ABS(decAmount) m
    from TableName
    where decAmount < 0
)
select distinct IsNull(p.intGroupID, n.intGroupID) as intGroupID
from pos p
    full join neg n on n.id = p.id and abs(n.m - p.m) < 1e-8
where p.m is NULL or n.m is NULL

to get unpaired elements, select satement can be changed to following:

select IsNull(p.intGroupID, n.intGroupID) as intGroupID, IsNull(p.m, -n.m) as decAmount
from pos p
    full join neg n on n.id = p.id and abs(n.m - p.m) < 1e-8
where p.m is NULL or n.m is NULL

Answer 6

Create TABLE #tblTest (
    intA INT
    ,decA DECIMAL(19,2)
);

INSERT INTO #tblTest (intA,decA)
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), (5,-5.0),(5,5.0) ;


SELECT * FROM #tblTest;

SELECT 
    intA
    , MIN(Result) as IsBalanced
FROM
(
    SELECT intA, X,Result =
          CASE
             WHEN count(*)%2 = 0 THEN 1
             ELSE 0
          END
    FROM
    (
       ---- Start thinking here --- inside-out
       SELECT 
          intA 
          , x = 
             CASE
                WHEN decA < 0 THEN
                    -1 * decA
                ELSE
                    decA
             END 
       FROM #tblTest
    ) t1
    Group by intA, X
)t2
GROUP BY intA