In SQLSMS v19.2 I see error after trying to wrap a SELECT to calculate SUM of an ALIAS column, why? what are the other methods I can try?
As per recommended in other help articles, I tried the following..
SELECT
os.order_number
,oj.job_number
,os.order_date
,op.product_code
,op.product_quantity
,cast(op.product_rate as DECIMAL(9,2)) as product_rate /* (to ensure rate is displayed 2 dec popnt) */
,op.product_quantity * op.product_rate AS product_total
,(SELECT SUM(product_total)) AS order_value
FROM order_sheet os
LEFT JOIN order_job oj ON os.order_number = oj.order_number
LEFT JOIN product_item op ON oj.order_job_number = op.order_job_number
GROUP BY os.order_number
ORDER BY item_total DESC
Invalid column name product_total.
I would like to find the numeric total for column named order_value and ideally I would like the result to be 2 decimal point like product_rate