CODEWITHSUNDEEP
sqloracle-database
bvh
bvh

Reputation: 443

SQL. Hide negative amounts if there are no corresponding positive amounts

I have an SQL statement that will hide the record if a positive amount has an equal negative amount. However, if I accidentally entered an incorrect negative amount, that negative amount would also show up in the report as a positive amount because I'm using the function ABS(). I don't want that.

For example, I have the following layout in my Oracle test table:

+------+---------+----------+-----------+
| user | project | contract | amount    |
+------+---------+----------+-----------+
| abc  | p123    | c123     |  100.00   |
+------+---------+----------+-----------+
| abc  | p123    | c123     |  200.00   |
+------+---------+----------+-----------+
| abc  | p123    | c123     |  150.00   |
+------+---------+----------+-----------+
| abc  | p123    | c123     | -150.00   |
+------+---------+----------+-----------+


SELECT user, project, contract, ABS(amount)
FROM test_table
WHERE contract = 'c123'
GROUP BY user, project, contract, ABS(amount)
HAVING COUNT(*) = 1;

The above SQL statement will return the first two records, which is what I want.

The issue I'm having is if the negative -150.00 incorrectly entered as -140.00, the above SQL statement will return all four records and negative -140.00 will become positive 140.00, which is not what I want. It should only show the first three records and hide the negative -140.00, or any negative amounts that don't have an equal positive amounts.

How can I hide negative -140.00 and only show the first third records and still be able to use GROUP BY and HAVING ?

Thank you in advance.

Upvotes: 0

Views: 215

Answers (4)

psaraj12
psaraj12

Reputation: 5072

You can use the below to get your result

        WITH data
         AS (SELECT *
             FROM   your table
             WHERE contract = 'c123'
              ),
         d2
         AS (SELECT Row_number()
                      over (
                        PARTITION BY user, project, contract,amount
                        ORDER BY user, project, contract) rw,
                    d.*
             FROM   data d)
    SELECT *
    FROM   (SELECT d.user1,
                   project1,
                   contract,
                   SUM(amount)
                     over(
                       PARTITION BY user, project, contract,Abs(amount), rw
                       ORDER BY user, project, contract) AS amount
            FROM   d2 d)
    WHERE  amount > 0;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Any approach that naively uses not exists or similar constructs is dangerous . . . for a simple reason: you could have duplicates.

Hence, I recommend using row_number() to enumerate the amounts and then use that:

with tt as (
      select tt.*,
             row_number() over (partition by user, project, contract, amount order by user) as seqnum
      from test_table tt
     ) tt
select tt.*
from tt
where not exists (select 1
                  from tt tt2
                  where tt2.user = tt.user and
                        tt2.project = tt.project and
                        tt2.contract = tt.contract and
                        tt2.seqnum = tt.seqnum and
                        tt2.amount = - tt.amount
                 );

Upvotes: 1

forpas
forpas

Reputation: 164099

You can do it with NOT EXISTS:

select t.* from test_table t
where t.contract = 'c123' and t.amount > 0
and not exists (
  select 1 from test_table
  where contract = t.contract and amount = -t.amount
)

or if only contract is not enough to define the row that you want:

select t.* from test_table t
where t.contract = 'c123' and t.amount > 0
and not exists (
  select 1 from test_table
  where "user" = t."user" and project = t.project 
    and contract = t.contract and amount = -t.amount
)

See the demo.

Upvotes: 0

Popeye
Popeye

Reputation: 35910

You can use exists as follows:

SELECT user, project, contract, ABS(amount)
FROM test_table t
WHERE contract = 'c123'
AND (amount >= 0 
     or exists (select 1 from test_table tin
                 where t.rowid <> tin.rowid
                   and t.contract = tin.contract 
                   and t.amount + tin.amount = 0)
    )

Upvotes: 0

Related Questions