15,507,372 members
See more:
How to calculate difference of purchase and Sale quantity in One Query Using Ms Access Database?

For Ex:
ProductId Type Quantity
1 Purchase 20
1 Sale 10

How to get difference of (20-10=10)in one Query?
Posted
Updated 16-Sep-13 0:37am
v3
Maciej Los 16-Sep-13 5:31am

## Solution 1

Something like this:
SQL
`SELECT P1.ProductId, (P1.Quantity - P2.Quantity) FROM MyProds as P1 INNER JOIN  MyProds as P2 ON P1.ProductId=P2.ProductId WHERE P1.Type='Purchase' AND P2.Type='Sale';`

should do the trick.

v2
Maciej Los 16-Sep-13 5:58am
Well done!
CPallini 16-Sep-13 6:02am
Thank you.
CPallini 16-Sep-13 7:42am
[Restoring the deleted, by mistake, OP comment]
It Does Not give me desired result,It Show me wrong value
i have a 24 purchase qty and 1 sale qty
It give me a result 21 and 1
NABIN SEN - 58 mins ago
CPallini 16-Sep-13 7:43am
It works for me on the example you provided. Could you please post here the exact set of inputs and ouputs?
NABIN SEN 16-Sep-13 7:49am
ProductId Type Quantity
1 Purchase 24
1 Sale 1