You're using PIVOT and conditional aggregation, but the PIVOT is removing the columns you're trying to use in the conditional aggregation.

I'm convinced you don't want PIVOT at all...

SELECT 
  s.SiteID, 
  a.AreaName, 
  SUM(CASE WHEN t.VisitCode = 1 THEN t.RequiredVisitDate END) AS [PM],
  COUNT(CASE WHEN t.VisitCode = 1 THEN 1 END) AS [PMCount],
  SUM(CASE WHEN t.VisitCode = 2 THEN t.RequiredVisitDate END) AS [CM],
  COUNT(CASE WHEN t.VisitCode = 2 THEN 1 END) AS [CMCount],      
  SUM(t.RequiredVisitDate) AS [Total]
FROM
  tblFuelTroubleTickets   AS t
INNER JOIN
  tblSites   AS s
    ON t.SiteCode = s.SiteCode
INNER JOIN
  tblAreas   AS a
    ON t.AreaCode = a.ID
WHERE
      t.RequiredVisitDate >= '2023-03-01' 
  AND t.RequiredVisitDate <  '2023-03-16'
GROUP BY
  s.SiteID, a.AreaName, s.SiteCode
ORDER BY
  [Total] DESC

I also removed your a, b, c aliases, and used letters that mean something, and improved the WHERE clause to be more robust.

To fix this error, you can include the "VisitCode" column in the SELECT statement of the inner query, like this:

SELECT
b.SiteID,
c.AreaName,
SUM(CASE WHEN sales_data.VisitCode = 1 THEN 1 END) AS [PM],
COUNT(CASE WHEN sales_data.VisitCode = 1 THEN 1 END) AS [PMCount],
SUM(CASE WHEN sales_data.VisitCode = 2 THEN 1 END) AS [CM],
COUNT(CASE WHEN sales_data.VisitCode = 2 THEN 1 END) AS [CMCount],
SUM(CASE WHEN sales_data.VisitCode = 1 THEN 1 END) + SUM(CASE WHEN sales_data.VisitCode = 2 THEN 1 END) AS [Total]
FROM (
SELECT
a.SiteCode,
a.VisitCode AS vc,
a.AreaCode,
a.RequiredFuelFilled,
a.RequiredVisitDate
FROM tblFuelTroubleTickets a
WHERE a.RequiredVisitDate BETWEEN '2023-03-01 00:00:00.000' AND '2023-03-15 23:59:59.999'
) sales_data
PIVOT (
SUM(RequiredFuelFilled)
FOR vc IN ([1], [2])
) AS PVTTABLE
INNER JOIN tblSites b
ON PVTTABLE.SiteCode = b.SiteCode
INNER JOIN tblAreas c
ON PVTTABLE.AreaCode = c.ID
GROUP BY b.SiteID, c.AreaName, b.SiteCode
ORDER BY [Total] DESC