15,393,638 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 173 Patrice T 60 Graeme_Grant 50 Richard MacCutchan 40 Richard Deeming 35
 OriginalGriff 1,644 Richard MacCutchan 900 Richard Deeming 340 0x01AA 295 Gerry Schmitz 290

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