SQL query not recognizing GROUP BY column name; created by case statement

Question

Hi,

I'm using SQL Server 2012. The result of my query below creates two rows for each site.  I would like to rollup the rows to show results on one row. However my GROUP BY statement with the column name Site is not being recognized. I'm receiving an error; MSG 207, Invalid column name 'Site'

Example

Current result:

                          Active          Construction

Denver                  2                      0

Denver                  0                      2

Would like to see:

Denver                  2                      2

I tried using GROUP BY but SQL is not recognizing my column heading called Site (created by case statement).  I need another person to look at this and show me where I'm going wrong.

Thank you in advance for your help!

Here is my code

SELECT

PersonDataID,

FirstName,

LastName,

PersonEmail,

SignMarkedBy,

DocumentType,

SignedDate,

DocumentSignMedium,

CASE

When SignMarkedBy

'denver123' then 'Denver'

When SignMarkedBy

'utah456' then 'Utah'

When SignMarkedBy

'sandeigo678' then 'SanDiego'  

When Locationid

'-1' then 'No Site Associated'

Else LEFT(Location.LocationName,CHARINDEX('-',Location.LocationName+'-')-1)

End as Site,

dbo.Company.CompanyName

FROM dbo.Status INNER JOIN PersonData on PersonData.PersonId = Status.PersonDataID

INNER JOIN dbo.Company on Person.CompanyId = Company.CompanyId

RIGHT OUTER JOIN Location on Status.LocationID = Location.LocationId

Where cast(SignedDate as date) BETWEEN '01/01/2016' and '01/23/2017'

GROUP BY Site

Order BY Site

Answer 1

Isn't that what the Pivot keyword is for?

Better to ask in a SQL Server forum. This one is about the Microsoft Access database product.