15,355,608 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

Top Experts
Last 24hrsThis month
 OriginalGriff 95 FreedMalloc 50 Chris Copeland 50 Tony Hill 30 Dave Kreskowiak 20
 OriginalGriff 455 CPallini 200 0x01AA 130 Daniel Pfeffer 70 FreedMalloc 60

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900