CODEWITHSUNDEEP
sqlsql-server-2008-r2
Randy B.
Randy B.

Reputation: 453

SQL: Subtracting from different tables and using GROUP BY

I've been trying to find an answer about this that solves my problem. I understand how to subtract from separate tables, but not in the context that I need it to work.

      SELECT I.IngredientName AS 'Ingredient Name', QOH AS 'Quantity On Hand',       
      SUM(BrewLine.Quantity) AS 'Total Ingredient Used'
      FROM INGREDIENT I
      INNER JOIN BrewLine 
      ON I.IngredientName = BrewLine.IngredientName 
      GROUP BY I.IngredientName, QOH

I need to have another field where I subtract QOH from SUM(BrewLine.Quantity) in order to have an, 'amount' remaining in the output. What is a way to go about this? If I just subtract them and then have to include them in GROUP BY. My output is all askew.

Thank you

Upvotes: 0

Views: 482

Answers (1)

Dave Markle
Dave Markle

Reputation: 97701

You could try using the partitioning functions instead of the grouping functions, which I think is what you're trying to accomplish...

SELECT 
    I.IngredientName AS 'Ingredient Name', 
    QOH AS 'Quantity On Hand',    
    QOH - SUM(BrewLine.Quantity) OVER(PARTITION BY I.IngredientName) AS 'Amount'
FROM INGREDIENT I
INNER JOIN BrewLine 
      ON I.IngredientName = BrewLine.IngredientName

Upvotes: 2

Related Questions