Justin Erswell Justin Erswell - 6 months ago 19
SQL Question

SQL Server SUM two values from other SUM's

I have this query,

SELECT
ORDUNIQ ,
SUM(UNITPRICE * ORIGQTY) AS 'LineTotal',
SUM(TAMOUNT1 + TAMOUNT2 + TAMOUNT3 + TAMOUNT4 + TAMOUNT5) AS 'TotalTax',
CAST('LineTotal' as numeric(9,2)) as [lTotal],
CAST('TotalTax' as numeric(9,2)) as [tTax],
SUM('lTotal' + 'tTax') AS 'OverallTotal'
FROM OEORDD
Group BY ORDUNIQ


I am trying to add the 'LineTotal' and the 'TotalTax' together however I am getting the following error


Operand Data type varchar is invalid for sum operator


Can anyone tell me how to achieve this properly?

Thanks

Justin

Answer

You can't reference a column alias at the same level as it is defined. Because you are delimiting the object identifiers in single quotes it assumes you are trying to sum a string literal.

You could put the query into a CTE to reuse the column alias and select from that.

;WITH T AS
(
SELECT      ORDUNIQ , 
            SUM(UNITPRICE * ORIGQTY) AS LineTotal, 
            SUM(TAMOUNT1 + TAMOUNT2 + TAMOUNT3 + TAMOUNT4 + TAMOUNT5) AS TotalTax
FROM         OEORDD
Group BY ORDUNIQ
)
SELECT *,
        LineTotal + TotalTax AS OverallTotal
FROM T