Use Aliases In Calculations In SQL Server 2008 I am attempting to run the below calculation, but I am getting an error of


  Msg 207, Level 16, State 1, Line 11
  Invalid column name 'Rpt1'.
  Msg 207, Level 16, State 1, Line 11
  Invalid column name 'Rpt2'.


This is the syntax I am trying to execute.


  Declare @saleID varchar(100), @totalsales int


Set @totalsales = (Select COUNT(numsales) 
                  FROM OPENQUERY(444.222.333.123, 'Select 
                                                   numsales, saleID 
                                                   FROM saledate')
                  WHERE saleID = @saleID)

Select
Rpt1 = count(case when empID = '1931' Then 1 else 0 end)
,Rpt2 = count(case when empID = '1831' Then 1 else 0 end)
,Balance = @totalsales-Rpt1-Rpt2
From totalsaleinfo
Where saleid = @saleid
Another approach is you can layer your queries as sub-queries, which allow you to capture all results, like so.  Also you most likely wanted to use "sum" instead of "count".

select Rpt1, Rpt2, Balance = @totalsales-Rpt1-Rpt2
FROM   (Select Rpt1 = sum(case when empID = '1931' Then 1 else 0 end)
        ,      Rpt2 = sum(case when empID = '1831' Then 1 else 0 end)
        From totalsaleinfo
        Where saleid = @saleid) AS sums