SQL Calculate Percentage on 2 columns

Question

I have a problem, I need to calculate the percentage between 2 different columns. Unfortunately I can't get it to work and when I run this all I get is "Invalid column name 'CountOfPlannedVisits'" & "Invalid column name 'CountOfPlannedVisitsClosed'"

SELECT       Count(*) As CountOfPlannedVisits, MAX(datename(month, dbo.tblVisit.DateConfirmed)) AS MonthName,
    SUM(CASE WHEN tblVisit.VisitTypeRef <> '5' THEN 1 ELSE 0 END) AS CountOfPlannedVisitsClosed, CAST(100.0 * SUM("CountOfPlannedVisits") / SUM(CountOfPlannedVisitsClosed) AS Decimal(5,2) ) As OverallAttendance
FROM            dbo.tblContract INNER JOIN
                         dbo.tblCustomer ON dbo.tblContract.CustomerRef = dbo.tblCustomer.CustomerID INNER JOIN
                         dbo.tblContractSite ON dbo.tblContract.ContractID = dbo.tblContractSite.ContractRef INNER JOIN
                         dbo.tblVisit ON dbo.tblContractSite.CardNumber = dbo.tblVisit.CardNumber
WHERE        (tblCustomer.CustomerNumber = '08434') 
AND (tblVisit.Routine = '1')
AND year(tblVisit.DateConfirmed) = Year('2013')--@DateYear)
AND  dbo.IsOnHoldEx(tblContract.OnHold, tblContractSite.OnHold, tblContract.OnHoldStartDate, tblContract.OnHoldEndDate, tblContractSite.OnHoldStartDate, tblContractSite.OnHoldEndDate) = 0  
AND tblVisit.Deleted = 0 -- make sure we dont pull through deleted visits
AND (tblContractSite.DateInactive is NULL or tblContractSite.DateInactive > GetDate()) 
GROUP BY  month(dbo.tblVisit.DateConfirmed)

Any help would be greatly appreciated as I'm not really sure where to go from here!

Thanks

Answer 1

You can only reference a column alias (like CountOfPlannedVisits in your case) in the order by clause. Anywhere else you have to repeat the expression or use a subquery table, something like :

select CountOfPlannedVisits, 
       CountOfPlannedVisitsClosed, 
       100 * CountOfPlannedVisits / CountOfPlannedVisitsClosed, ...
from (
  select some_expression as CountOfPlannedVisits ,
  some_other_expression as CountOfPlannedVisitsClosed
  ....
) a_table
....