Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2014
    Posts
    1

    Unanswered: Query to calculate balance

    Hi, I have two tables in my database.

    Table inventory:
    Code:
    inv_id  |  item     |   qty
    1           candy1      10
    2           candy2      20
    3           candy3      30
    4           candy4      30

    Table orders:
    Code:
    od_id  |  inv_id(fk)  |  qty
    1              1          3
    2              1          3
    3              4          8

    I want to create a query to display results:

    Table inventory:
    Code:
    inv_id  |  item     |   qty   | ordered qty |  balance | 
    1           candy1      10            6              4
    2           candy2      20            0              20
    3           candy3      30            0              30
    4           candy4      30            8              22
    thanks,

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Code:
    SELECT I.inv_id,  I.item, I.qty as OpeningBalance, O.Qty, (I.qty - O.qty) as closingBalance
     FROM inventory as I
    left join orders as O on I.inv_id = O.inv_ID
    order by inv_id
    you may need to tweek the join to make certain you extract ALL thge rows from inventory and jsut thise rows from orders where the inv_id is equal

    ypou may wnt to refine the wuery by using a sub query to SUM the qty sold in orders
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •