15,398,348 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 15-Sep-13 23:37pm
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
CPallini 16-Sep-13 8:01am

Try this
"BEGIN TRANSACTION;

CREATE TABLE MyProds (ProductId integer, [Type] varchar, Quantity integer);
INSERT INTO MyProds VALUES (1, 'Purchase', 24);
INSERT INTO MyProds VALUES (1, 'Sale', 1);
COMMIT;

SELECT DISTINCT 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';"

on
http://www.compileonline.com/execute_sql_online.php