How to calculate the difference between purchase and sale quantity?

Question

How can I calculate the difference between the purchase and sale quantity in one query using Ms Access database?

My data, for example, looks like this:

ProductId Type Quantity
1         Purchase 24
1         Sale      1

How would I get the difference of (24-1=23) in one query?

Does `"SELECT (SELECT Quantity FROM [table] WHERE ProductId=@productId AND Type='Purchase') - (SELECT Quantity FROM [table] WHERE ProductId=@productId AND Type='Sale')"` work? - or maybe something else from here http://stackoverflow.com/questions/2611174/how-do-i-subtract-values-from-two-select-statements or here http://stackoverflow.com/questions/1589070/subtraction-between-two-sql-queries — Corak, Sep 16 '13 at 10:57
It Gives me Error: At most one record can be returned by this subquery — user2674803, Sep 16 '13 at 11:02

knittl · Answer 1 · 2013-09-16T13:41:05.407

0

You can self-join the table:

SELECT p.productId, (p.quanity - COALESCE(s.quantity, 0)) difference
FROM table p
LEFT JOIN table s
ON p.type = 'Purchase' AND s.type = 'Sale' AND p.productId = s.productId

edited Sep 16 '13 at 13:41

answered Sep 16 '13 at 11:03

knittl

246,190
53
318
364

i Have only One table that contain Purchase And Sale Product – user2674803 Sep 16 '13 at 12:03
@user2674803: yes, I know. My answer uses only that table – knittl Sep 16 '13 at 12:22
it Gives Me Error: Join expression not supported. – user2674803 Sep 16 '13 at 12:33
1

@user2674803: I'm pretty sure that even MS Access can handle joins. – knittl Sep 16 '13 at 13:32
i am solved the error.But it does not show any value if there is no sale value of that product.How to solve this? – user2674803 Sep 16 '13 at 13:35
Use `coalesce` function. – knittl Sep 16 '13 at 13:41

score 0 · Answer 2 · answered Sep 16 '13 at 11:12

I suppose you have the database name [DB-NAME]. and Columns and rows are something like.

[Table1]
ProductID      Quantity         Purchase         Sale
-----------    ---------        ---------        --------
1                1                 24              1
2                100               50              10

If you want to calculate the [Purchase] - [Sale] for a specific Product id Use:

( Select (Purchase - Sale) AS MyNumber FROM[DB-Name].[Table1] WHERE (ProductID=1))

//where 1 is your product id

The result table will be

MyNumber
--------
23

if you want to calculate the totals for all [ProductID] Use:

  (Select (SUM(Purchase) - Sum(Sale)) AS MyNumber FROM[DB-Name].[Table1] )

The result table will be

MyNumber
--------
63

There is No Purchase AND Sale Columns in my table,There Is Type Columns that conatails a row Purchase And Sale — user2674803, Sep 16 '13 at 11:51

How to calculate the difference between purchase and sale quantity?

2 Answers2

Linked

Related