jason jason - 21 days ago 7
SQL Question

Sum of two calculated columns in SQL

I'm calculating two rows like this :

CASE
WHEN isnumeric(SUBSTRING(dbo.Table.RNumber,1,CHARINDEX('+',dbo.Table.RNumber) - 1)) = 1
THEN SUBSTRING(dbo.Table.RNumber,1,CHARINDEX('+',dbo.Table.RNumber) - 1)
else 0
end AS RoomNumber,
CASE
WHEN isnumeric(SUBSTRING(dbo.Table.R.Number,CHARINDEX('+',dbo.Table.RNumber) + 1,LEN(dbo.Table.R.Number))) = 1
THEN SUBSTRING(dbo.Table.R.Number,CHARINDEX('+',dbo.Table.RNumber) + 1,LEN(dbo.Table.R.Number))
else 0
end AS HallNumber,


I need to make another column that calculates the sum of RoomNumber and HallNumber, I tried this :

SELECT *, (RoomNumber + HallNumber) AS Sum
FROM Table


But I got invalid column name error.
How can I get the sum of these two columns? Thanks.

Answer

You really only have two options since you can't reuse an alias at the same level in which it was defined. Either you can just add together the two CASE statements:

CASE WHEN isnumeric(SUBSTRING(dbo.Table.RNumber,1,CHARINDEX('+',dbo.Table.RNumber) - 1)) = 1
     THEN SUBSTRING(dbo.Table.RNumber,1,CHARINDEX('+',dbo.Table.RNumber) - 1) 
     ELSE 0 END AS RoomNumber,
CASE WHEN isnumeric(SUBSTRING(dbo.Table.R.Number,CHARINDEX('+',dbo.Table.RNumber) + 1,LEN(dbo.Table.R.Number))) = 1
     THEN SUBSTRING(dbo.Table.R.Number,CHARINDEX('+',dbo.Table.RNumber) + 1,LEN(dbo.Table.R.Number)) 
     ELSE 0 END AS HallNumber,
(CASE WHEN isnumeric(SUBSTRING(dbo.Table.RNumber,1,CHARINDEX('+',dbo.Table.RNumber) - 1)) = 1
     THEN SUBSTRING(dbo.Table.RNumber,1,CHARINDEX('+',dbo.Table.RNumber) - 1) 
     ELSE 0 END
+
CASE WHEN isnumeric(SUBSTRING(dbo.Table.R.Number,CHARINDEX('+',dbo.Table.RNumber) + 1,LEN(dbo.Table.R.Number))) = 1
     THEN SUBSTRING(dbo.Table.R.Number,CHARINDEX('+',dbo.Table.RNumber) + 1,LEN(dbo.Table.R.Number)) 
     ELSE 0 END) AS Sum

Or you can try wrapping your current query as a subquery, where the aliases you already defined would be available:

SELECT t.RoomNumber,
       t.HallNumber,
       (t.RoomNumber + t.HallNumber) AS Sum
FROM
(
    ... your original query ...
) t
Comments