CODEWITHSUNDEEP
sqlsql-server
Justin Erswell
Justin Erswell

Reputation: 708

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

Upvotes: 1

Views: 13384

Answers (3)

Soliman Soliman
Soliman Soliman

Reputation: 159

Operand Data type varchar is invalid for sum operator, all this means is you can not add a varchar to an integer. In your sum statement all columns should be of type integer and none should be of type varchar. So I would check each column type that you have put into the sum statement for starters.

Upvotes: 0

Mark Storey-Smith
Mark Storey-Smith

Reputation: 997

; WITH taxCTE
AS 
(
SELECT
    ORDUNIQ
    , SUM(UNITPRICE * ORIGQTY) AS 'LineTotal'
    , SUM(TAMOUNT1 + TAMOUNT2 + TAMOUNT3 + TAMOUNT4 + TAMOUNT5) AS 'TotalTax'
FROM
    OEORDD
GROUP BY
    ORDUNIQ
)

SELECT
    CAST(LineTotal AS NUMERIC(9, 2)) AS [lTotal]
  , CAST(TotalTax AS NUMERIC(9, 2)) AS [tTax]
  , SUM(CAST(LineTotal AS NUMERIC(9, 2)) + CAST(TotalTax AS NUMERIC(9, 2))) AS 'OverallTotal'
FROM
    taxCTE ;

You can't reference the aliased columns in the way you were in your original query. Instead use a sub-query, or as above a CTE.

Upvotes: 6

Martin Smith
Martin Smith

Reputation: 453278

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

Upvotes: 5

Related Questions