0

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
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

  • 3
    The count(case when ... then 1 else 0 end) won't give what they expect. Use either sum(case when ... then 1 else 0 end) or count(case when ... then 1 else null end) – ypercubeᵀᴹ May 8 '17 at 19:38
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.