subtract 2 columns values from two different table

  • Hello, Please need your help.

    I have 3 tables.

    * Product_Table (id_product, product_name)

    * Sold_Items (id_product, quantity, date_sold)

    * Returned_Items (id_product, quantity, date_returned)

    how can I query in order to get results like:

    id_product / product_name / sum(sold_quantity) / sum(Returned_quantity) / net_amount (sold minus returnet quantity)

    1 / Notebook / 50 / 10 / 40

    each query sum for sold and returned quantity has to be parametized by different dates. (date_sold, date_returned)

    Thanks in advance.

    Jon

  • Outer join Products to the Sales and Returns tables. Do a totals query. Then you'll get a record for every Product regardless of whether it has had any sales or returns. Then just create another query based on the totals query and subtract.

  • Heya!!

    You could try some fancy SQL, be be aware some of this TSQL code does different things with different versions of SQL, but I am hoping you are using at-least 2005 in which case give the following bit of code a whirl.

    SELECT P.ID_Product, P.Product_Name, Sold.Sold_Quantity, Returned.Returned_Quantity,

    COALESCE(Sold.Sold_Quantity,0) - COALESCE(Returned.Returned_Quantity) as [Net_Amount]

    from Products P

    LEFT JOIN (select SUM(Quantity) as Sold_Quantity, ID_Product from Sold_Items GROUP BY ID_Product) Sold ON P.ID_Product = Sold.ID_Product

    LEFT JOIN (select SUM(Quantity) as Returned_Quantity, ID_Product from Returned_Items GROUP BY ID_Product) Returned

    ON P.ID_Product = Returned.ID_Product

    If it doesn't look like what you want let me know and I will start thinking again!!

    Sam

  • thank you so much samantha

    It worked! really appreciated

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply