Adding Calculated Columns together to create a Total I am creating a tracking sheet, based off of multiple tables. I then use a CASE statement to determine the progress of each section. I would like to then take the results of these 3 calculated col and create a Total ?

Select Case
     ....
End as [Val1],
Select Case
     ....
End as [Val2],
(Val1 + Val2) as [Total]
From Table


Currently the Addition line is not working since the Val1 and Val2 are 'Invalid Column Names'

How can I fix this ?

SELECT 
    d.DataSourceID as [DataSource], 
    c.Name as [Custodian Name], 
    et.Name as [Source Type], 
    ed1.QCBarcode as [ED1 QC Barcode], 
    ed1code.Name as [ED1 Status],
Case 
    When ed1code.Name like '01%' then 14
    When ed1code.Name like '02%' then 28
    When ed1code.Name like '03%' then 42
    When ed1code.Name like '04%' then 56
    When ed1code.Name like '05%' then 71
    When ed1code.Name like '06%' then 85
    When ed1code.Name like '07%' then 100
    When ed1code.Name like '08%' then 0
End  as [ed1Calc],
ed3.ProcessingCase as [ED3 LAW Case Name],  
ed3code.Name as [ED3 Status], 
Case 
    When ed3code.Name like '01%' then 50
    When ed3code.Name like '02%' then 100
    When ed3code.Name like '03%' then 0
    When ed3code.Name like '04%' then 0
End  as [ed3Calc],
ed4.ED4ID as [ED 4 Reference],  
ed4code.Name as [ED4 Status],
Case 
    When ed4code.Name like '01%' then 34
    When ed4code.Name like '02%' then 66
    When ed4code.Name like '03%' then 100
End  as [ed4Calc],
 ( (ed1Calc + ed3Calc + ed4Calc)/3 ) as [Overall Status]
/*Basic  layout */
FROM [EDDSDBO].[DataSources] d
JOIN  [EDDSDBO].[Custodian] c ON d.Custodian = c.ArtifactID
JOIN [EDDSDBO].[EvidenceType] et ON d.EvidenceType= et.ArtifactID
JOIN [EDDSDBO].[f4868106f4868107] fed1 on fed1.f4868107ArtifactID = d.ArtifactID
JOIN [EDDSDBO].[ED1PreProcessingRecord] ed1 ON fed1.f4868106ArtifactID = ed1.ArtifactID
JOIN  [EDDSDBO].[ED3LawLoadingRecord] ed3 ON ed1.ArtifactID = ed3.ED1Reference
JOIN [EDDSDBO].[ED4LawRelativityMigrationRecord] ed4 ON ed3.ArtifactID =    ed4.ED3Reference
 /*status*/
--ed1
JOIN [EDDSDBO].[ZCodeArtifact_1000129] ed1Status  ON ed1.ArtifactID =  ed1Status.AssociatedArtifactID
JOIN  [EDDSDBO].[Code] ed1code ON ed1Status.CodeArtifactID = ed1code.ArtifactID

--ed3
JOIN [EDDSDBO].[ZCodeArtifact_1000156] ed3Status  ON ed3.ArtifactID = ed3Status.AssociatedArtifactID
JOIN  [EDDSDBO].[Code] ed3code ON ed3Status.CodeArtifactID = ed3code.ArtifactID
--ed4
JOIN [EDDSDBO].[ZCodeArtifact_1000161] ed4Status ON ed4.ArtifactID = ed4Status.AssociatedArtifactID
JOIN  [EDDSDBO].[Code] ed4code ON ed4Status.CodeArtifactID = ed4code.ArtifactID
You don't specify any specific RDBMS.

A method that should work in all/most would be to use a derived table.

SELECT Val1,
       Val2, 
       Val1 + Val2 AS Total
FROM 
(
Select Case
     ....
End as [Val1],
Select Case
     ....
End as [Val2]
From Table
) d /*&lt;-- arbitrary derived table alias*/


Probably you are on SQL Server (from the square brackets) in which case you could also use

SELECT Val1,
       Val2,
       Val1 + Val2 AS Total
FROM   TABLE
       CROSS APPLY (VALUES(Case ....End,Case ....End)) V( Val1, Val2) 


depending on the exact expression behind Val1 and Val2 (e.g. you can't use this and get correct results if they reference window functions).