CODEWITHSUNDEEP
mysqlsql
HankBoo
HankBoo

Reputation: 76

SQL group by rows negate by a field

I have a table transactions below:

| id | type | transaction |
| 1  | A    | 100         |
| 2  | B    | -500        |
| 3  | A    | 300         |
| 4  | A    | 400         |
| 5  | A    | 500         |
| 6  | B    | -300        |
| 7  | B    | -100        |
| 8  | A    | 100         |
| 9  | B    | -100        |
| 10 | A    | 100         |
| 11 | B    | -100        |
| 12 | A    | 100         |

Essentially for each type A transaction the transaction amount will be positive, and for each type B transaction the transaction amount will be negative. (B is a reversal transaction of A).

What's the best way to write a sql select statement to filter out all transactions as well as their negated transactions, and only leave the ones that aren't reversed. e.g.

| id | type | transaction |
| 4  | A    | 400         |
| 12 | A    | 100         |

ps. There won't be any B type transactions without A.

Upvotes: 0

Views: 125

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270553

If there is a 1-1 correspondence, then not exists works pretty well:

select t.*
from transactions t
where not exists (select 1
                  from transaction t2
                  where t2.type <> t.type and t2.transaction = - t.transaction
                 );

This specifically fails when one transaction can have multiple amounts that are the same. For that, I might recommend aggregation instead:

select amount, sum(type = 'A') as num_as, sum(type = 'B') as num_bs
from transactions t
group by abs(amount)
having sum(amount) <> 0;

This doesn't return the original rows, but it does show where things don't line up.

Upvotes: 1

Related Questions