Group By Inventory

snail1984 · #1 (**permalink**) 10-25-09, 09:35

Code:

SELECT ( SELECT sum(Received)
           FROM InventoryActivity 
        )
     - ( SELECT sum(Used)
           FROM InventoryActivity
        ) AS Onhand

r937 · #2 (**permalink**) 10-25-09, 09:52

Code:

SELECT ProductID 
     , SUM(Received) AS received
     , SUM(Used) AS used
     , SUM(Received) -
       SUM(Used) AS onhand
  FROM InventoryActivity 
GROUP
    BY ProductID

snail1984 · #3 (**permalink**) 10-25-09, 09:53

That was quick! Thanks for your help!

snail1984 · #4 (**permalink**) 10-25-09, 14:04

I also have a trigger number in a product table:
ID | Name | Category | Trigger

How would the query go after that if I wanted:
Display all ProductIDs and ProductNames if the onhand is <= trigger? So I can tell what I need to order based on this.

Internet Services

Web Development

Digital Marketing

Consumer Tech