How to subtract 2 columns values resulting from two different queries

• jo-283369

  Old Hand

  Points: 377

  More actions

  June 5, 2011 at 6:39 am

  #75597

  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

• Ulysses_Infinity

  SSCrazy

  Points: 2588

  More actions

  June 6, 2011 at 2:00 am

  #1334222

  Assuming you want NULLs for products not sold or returned, try something along the lines of:

  select pt.id_product,

  pt.product_name

  sum(si.quantity),

  sum(ri.quantity),

  sum(si.quantity) - sum(ri.quantity)

  from product_table pt

  left join sold_items si on pt.id_product = si.id_product

  left join returned_items ri on ri.id_product = pt.id_product

  group by pt.id_product,

  pt.product_name

  and alias accordingly. This does of course assume your 'quantity' fields are numeric and therefore SUMable.