none
how to sum a value of a column of a table in multiple table into one select command into a stored procedure?

    Question

  • good day to the experts,

    how could i sum a value of all column of a table in multiple table into one select command into a stored procedure?

    below is my SELECT command for each parent/child table

    1.) SELECT sum(product.beg_bal) ast prodttlQty FROM products WHERE products.item_code=='00011244'


    2) SELECT  sum(purchase.unit_qty) as purchttlqty FROM purch_hd inner join purchase
    ON purch_hd.or_number=purchase.or_number WHERE purchase.item_code='00011244'

    3) SELECT  sum(chrgchild.unit_qty) as chrgttlqty FROM chrgheader inner join chrgchild
    ON chrgheader.or_number=chrgchild.or_number WHERE chrgchild.item_code='00011244'

    4) SELECT  sum(cashchild.unit_qty) as cashttlqty FROM cashheader inner join cashchild
    ON cashheader.or_number=cashchild.or_number WHERE cashchild.item_code='00011244'

    5) SELECT  sum(dlvrychild.unit_qty) as dlvryttlqty FROM dlvryheader inner join dlvrychild
    ON dlvryheader.or_number=dlvrychild.or_number WHERE dlvrychild.item_code='00011244'

    6) SELECT  sum(consignedchild.unit_qty) as consignedttlqty FROM consignedheader inner join consignedchild
    ON consignedheader.or_number=consignedchild.or_number WHERE consignedchild.item_code='00011244'

    totalQty=(prodttlQty+purchttlQty)-(chrgttlQty+cashttlQty+dlvryttlQty+consignedttlqty)

    now, is there a way i could make one single select command for that in a stored procedure? which the result will only give me the total quantity?

    thank you very much in advance to everyone for any help

     


    .madix_t
    Saturday, August 14, 2010 1:49 AM

Answers

  • Hi,

      You can customize the query according to the following example:

    select q1.a + q2.b 
     from
    (select sum(id) a from t1 )q1,
     (select sum(id) b from t2) q2
    

    -- Reddy Balaji C.

    ##Mark as answer if this solves the problem

    • Marked as answer by Madix_t Saturday, August 14, 2010 11:13 AM
    Saturday, August 14, 2010 3:03 AM

All replies

  • Hi,

      You can customize the query according to the following example:

    select q1.a + q2.b 
     from
    (select sum(id) a from t1 )q1,
     (select sum(id) b from t2) q2
    

    -- Reddy Balaji C.

    ##Mark as answer if this solves the problem

    • Marked as answer by Madix_t Saturday, August 14, 2010 11:13 AM
    Saturday, August 14, 2010 3:03 AM
  • thank you very much for your immediate reply reddy, i try your example and it works fine, but how could i address the error NULL when if one select has a value NULL?, because i got a null value of the the total if one of the select has no data found.?

    thank you
    .madix_t
    Saturday, August 14, 2010 9:18 AM
  • awwsss.. it's ok now reddy thank you very, very much for your help, i just put ISNULL to the total.... again thank you.

    select isnull(q1.a,0.00) + isnull(q2.b,0.00) AS ttl
    from
    (select sum(id) a from t1 )q1,
    (select sum(id) b from t2) q2

    have a nice day....


    .madix_t
    Saturday, August 14, 2010 9:48 AM