Use Aliases In Calculations

Question

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

Answer 1

You will need to store the rpt1 and rpt2 as variables then subtrack from total, not as the aliases.

Or perform:

Select
@totalsales-
(count(case when empID = '1931' Then 1 else 0 end))
-
(count(case when empID = '1831' Then 1 else 0 end))
as Balance
From totalsaleinfo

Where saleid = @saleid

Answer 2

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