How to get the remainder of each item in SQL?

user_14535309 0
Viewed 103

I have a table that has these columns:

  • Product Title
  • Product Sort
  • Quantity ( can be both income and outcome )
  • Price
  • IncomeOutcome ( can be 1 or 2, 1 means Income 2 means Outcome )

And I need to query the remainder of items for each of my individual Products.

For example I have the following rows:

Title Sort Quantity Price IncomeOutcome
Beer Light 100 8$ 1
Beer Light 60 8$ 2
Beer Dark 80 9$ 1
Beer Dark 50 9$ 2

1st row means that I'm receiving 100 Dark Beer for 8$

2nd row means that I'm selling 60 Dark Beer for 8$

How can I write a query that would return a row like this for each one of my table rows:

Title Remainder
Light Beer 40
Dark Beer 30
sql-server sql
1 Answers

Try this:

SELECT 
   Title
   , Sort
   , SUM(Quantity * IIF(IncomeOutcome = 1, 1, -1)) AS Remainder
FROM t
GROUP BY Title, Sort
user_21497180
Related