Calculate a field based on 2 calculated colums in SQL

Question

I have a query that sums a couple of values based on a common identifier known as a workcell. I'm trying to figure out how to add a column that calculates this formula as a percentage: (SumOfAct - SumOfStd) / (SumOfStd)

I was thinking some kind of subquery with inner joins would work, but I'm not sure how to get it looking right.

Here is my code that gets everything I want except for that calculated column:

SELECT v_MES_OrderIssues.AssignedWorkcell
 , CONVERT(Decimal(10,2), Sum(v_SAP_OrderOperations.Std)) AS SumOfStd
 , CONVERT(Decimal(10,2), Sum(v_SAP_OrderOperations.Act)) AS SumOfAct
 , CONVERT(Decimal(10,2), Sum(v_SAP_OrderOperations.Variance)) AS SumOfVariance

FROM (v_SAP_OrderOperations 
LEFT JOIN v_SAP_Orders ON v_SAP_OrderOperations.Ordr = v_SAP_Orders.Ordr) 
LEFT JOIN v_MES_OrderIssues ON v_SAP_OrderOperations.Ordr = v_MES_OrderIssues.WOrder
WHERE (((v_SAP_Orders.OpenOrder) Like '1')
AND ((v_SAP_Orders.Equipment) Is Not NULL)
AND ((v_SAP_OrderOperations.ACT)>0))
AND ((v_MES_OrderIssues.AssignedWorkcell) Like 'S5H%W')
AND ((v_MES_OrderIssues.DateTimeClosed) Is Null)
OR (((v_SAP_Orders.OpenOrder) Like '1')
AND ((v_SAP_Orders.Equipment) Is Not NULL)
AND ((v_SAP_OrderOperations.OpenOp) Like '0'))
AND ((v_MES_OrderIssues.AssignedWorkcell) Like 'S5H%W')
AND ((v_MES_OrderIssues.DateTimeClosed) Is Null)

GROUP BY v_MES_OrderIssues.AssignedWorkcell

ORDER BY Sum(v_SAP_OrderOperations.Variance) DESC

Answer 1

If I got it right yo can do it directly in SELECT clause

SELECT v_MES_OrderIssues.AssignedWorkcell
 , CONVERT(Decimal(10,2), Sum(v_SAP_OrderOperations.Std)) AS SumOfStd
 , CONVERT(Decimal(10,2), Sum(v_SAP_OrderOperations.Act)) AS SumOfAct
 , CONVERT(Decimal(10,2), Sum(v_SAP_OrderOperations.Variance)) AS SumOfVariance
 , CONVERT(Decimal(10,2), (Sum(v_SAP_OrderOperations.Act) - Sum(v_SAP_OrderOperations.Std))/ Sum(v_SAP_OrderOperations.Std)) AS percentage
...

BTW,

LEFT JOIN v_SAP_Orders 
...
WHERE (((v_SAP_Orders.OpenOrder) Like '1')

will be INNER JOIN really as a column from left-joined table expression is thus prohibited to have NULL value. You may wish to move the predicate to ON clause to keep it left-joined.