I have a query that is giving me issues with regards to getting a running total everytime new inventory is added. I have 2 tables, stock_receiving and stock_items. Stock receiving is where is record is listed when a part is received for a particular contract (division of our company). It referenced by rcv_id. Stock_items is where all of the current quantities are of each part by contract. Stock items can have the same part listed multiple times based on the number of divisions that use the part and each location that each division warehouses them at. Example contract A can have stock number 1234 in location 1 and 2 and contract B can have stock 1234 in location 2 and 3. Even though the same part is in location 2 the quanities have to be kept separate as they belong to two different groups. We do this by having 4 entries in stock items: "stock1234 for division A in location 1", "stock1234 for division A in location 2", "stock1234 for division B in location 2", and "stock1234 for division B in location 3", each with their respective quantities. When the item is received an entry goes in the stock_receiving table for that receipt and the qty in stock_items is increased based on the location, division and stock_number so we know how much of each part, at each location, each division has.
When we are receiving it is based solely on the division. So if division A wants to receive STK1234 with a QTY of 100 in location 1, I need to show the total qty now available to division 1 from all of their locations in a column on that receiving screen.
Here is what I use to pull the records on the receiving document with id 12357:
SELECT stock_receiving.STOCK_NO, stock_receiving.PART_NO, stock_receiving.QTY, stock_receiving.CONTRACT_ID, stock_receiving.RCV_ID
Here is what I came up with to pull the total qty of stock 8167 for division 331
SELECT sum(stock_items.qty) as totalqty, stock_items.stock_no
WHERE stock_no = 'STK8167' and contract_id = '331'
group by stock_no
They both give me the correct results but I can't figure out how to tie them together in a single query to give me the running tally on the receiving screen each time I add an entry.