I have a table as shown below. I want to do a partition and then subtract the values in the same column and different rows to get the difference using group by.
| id | type | name | amount |
|---|---|---|---|
| 1 | sale | sam | 2 |
| 2 | sale | sam | 15 |
| 3 | return | lilly | 20 |
| 4 | sale | lilly | 25 |
| 5 | return | sam | 3 |
| 6 | sale | anju | 20 |
And Need to return:
| name | amount |
|---|---|
| sam | 14 |
| lilly | 5 |
| anju | 20 |
CodePudding user response：
Your sample data is a little suspect but I think you basically need to apply a case expression to your sale type:
select name,
Sum(amount * case when type='return' then -1 else 1 end) Amount
from t
group by name;