15,389,911 members
1.00/5 (2 votes)
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
Comments
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
Comments
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
 Richard MacCutchan 125 OriginalGriff 105 0x01AA 75 Gerry Schmitz 55 Patrice T 45
 OriginalGriff 990 Richard MacCutchan 515 0x01AA 265 Gerry Schmitz 250 Maciej Los 190

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