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

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

Answer 2

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