Anju L S
Anju L S

Reputation: 37

Calculating the Difference Between Two Values in the Same Column using group by in mysql

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

Upvotes: 0

Views: 573

Answers (1)

Stu
Stu

Reputation: 32579

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;

Upvotes: 1

Related Questions