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

The question is: how to help you without any background information?

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

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Top Experts
Last 24hrsThis month
 OriginalGriff 180 Richard MacCutchan 60 Peter_in_2780 40 Patrice T 10 CHill60 10
 OriginalGriff 2,392 Richard MacCutchan 1,190 Richard Deeming 485 0x01AA 335 Patrice T 314

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