Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2009
    Posts
    6

    Unanswered: Group By Inventory

    I have a table set up with like this

    ActivityID | ActivityDate | ActivityDesc | ProductID | OrderNum | Ordered | Received | Used | SupplierID

    I need to make a query so I can subtract the Used from the Received, and group the results by the ProductID so I can see what I have onhand for all products. This is what I have so far. Can someone please help me with this?

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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT ProductID 
         , SUM(Received) AS received
         , SUM(Used) AS used
         , SUM(Received) -
           SUM(Used) AS onhand
      FROM InventoryActivity 
    GROUP
        BY ProductID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2009
    Posts
    6
    That was quick! Thanks for your help!

  4. #4
    Join Date
    Oct 2009
    Posts
    6
    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.

Posting Permissions

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