Should I use Group By??

I have an inventory tracking system that has a layered inventory built in. I have basically Two tables, Items and Inventory.

The Item table is just that, a list of all of my items. The inventoy table tracks the current quantity and on-hands. (Qty is how many are in the building, on-hand is how many are available for sale)

The inventory table has Receive Date, Item_id, QTY, On_Hand, Slot and Cost_per_item.

The idea is to keep a record of each receiver into my warehouse. (Transfers and sales are kept in a different table.)

I want to create a report for the stock people to reconcile their inventory.

When a new receiver comes in if the item_id and cost_per_item are the same then it just updates the QTY and On_Hand. If the cost_per_item is different then it inserts a new record.

Now if the same item with two different costs are in the same slot location, then it will produce two lines. I'm trying to figure out how I can set up my Select to group the items in a slot so the warehouse person doesn't have to add them up before he counts the stock.

I tried Group By, but I couldn't seem to get it to work. Right now I have

SELECT *
FROM inventory
ORDER BY slot

The other thing I thought about was to do the query as above and then when there are two like items in the same slot add them together and display that. That seems like sloppy code and I see some opportunities for bugs to be introduced into the system.



To end the whole thing off, if there are two items in the slot, both with a qty of 10 and the warehouse person counts 18 total, I plan to reduce the older stock by 2 to bring the number back to true.

I thought layered inventory would be a nice thing to have, all the little nuances really make it a pain! Thanks for the help!

in your select specify the column you want to group on,

SELECT * , slot
FROM inventory
ORDER BY slot
or

SELECT slot, item2, item3
FROM inventory
ORDER BY slot

depending on your db

The GROUP BY clause is closely connected to aggregates, you can only group on things you specifically select

cheers
RK