Sum and group two value of two table

[hussein2000]

Hello friend

i have two table purchase and Sales all of two tables have ItemId and quantity fields

so i want to get the balance after sales

ex purchased quantity is 50 sold quantity 20 balance 30 for each itemid

thanks

[hspc]

This query can do the job:
Select ItemID,Name
,((Select case When Sum(quantity) is null then 0 else Sum(quantity) End from purchase where ItemID=I.ItemID)
- (Select case When Sum(quantity) is null then 0 else Sum(quantity) End from sales where ItemID=I.ItemID)) as balance
From Items I

[hussein2000]

THANKS FRO YOUR POSTED REPLY
I could not understand the query actualy i have not seen sql statement like this

anyway i try to execute it but no result i mean i got an error

and what is Item I .


thanks

[hspc]

anyway i try to execute it but no result i mean i got an error

What error did you get ?
What database do you use ? (this should work fine on MS SQL server and MySQL)
did you rename the tables in my query to the names you use in your database ?
and what is Item I
Items is the name of the table that I assumed that it contains the items.
Items I : means that I give the table Item the alias I..I use this alias in the 2 subqueries.

[hussein2000]

dear friend

thanks for reply.
i am using ms access as database.

i have tow table not three purchase and sales with itemid,quantity fields

and i want the balance of purchase after sales



thanks