Probably an easy one, need help with a Where clause

  • Ok, I'm not sure how to include something in a where clause....probably easy to do and I'm not looking in the right places or something. So here is my statement that I'm want

    @Inst_ID int,

    @status char(1)

    SELECT

    Inventory.Car,

    Inventory.StockNumber,

    SUM(InventoryService.FinalCost) As TotalCost

    FROM Inventory

    LEFT JOIN InventoryService ON Inventory.Inventory_ID = InventoryService.Inventory_ID

    WHERE

    Inventory.Inst_ID = @Inst_ID AND

    Inventory.Status LIKE @status AND

    SUM(InventoryService.FinalCost) > 0.00 --this part is wrong of course

    So, my sum statement doesn't work in my WHERE clause, how do I make it work?

  • Ok I think I figured out what I need...

    @Inst_ID int,

    @status char(1)

    SELECT

    Inventory.Car,

    Inventory.StockNumber,

    SUM(InventoryService.FinalCost) As TotalCost

    FROM Inventory

    LEFT JOIN InventoryService ON Inventory.Inventory_ID = InventoryService.Inventory_ID

    WHERE

    Inventory.Inst_ID = @Inst_ID AND

    Inventory.Status LIKE @status

    GROUP BY Inventory.Car, Inventory.StockNumber

    HAVING SUM(InventoryService.Finalcost) > 0.00

  • Sometimes it just comes to you. ANy aggreagate function that needs to be in the GROUP BY will need to have it's criteria in the HAVING clause.:hehe:

    Kindest Regards,
    David

    ** Obstacles are those frightening things that appear when we take our eyes off the goal. **

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply