i am using this query to get the Sum and Count of Visits
SELECT
b.SiteID,
c.AreaName,
SUM(CASE WHEN VisitCode = 1 THEN 1 END) AS [PM],
COUNT(CASE WHEN VisitCode = 1 THEN 1 END) AS [PMCount],
SUM(CASE WHEN VisitCode = 2 THEN 1 END) AS [CM],
COUNT(CASE WHEN VisitCode = 2 THEN 1 END) AS [CMCount],
SUM(CASE WHEN VisitCode = 1 THEN 1 END) + SUM(CASE WHEN VisitCode = 2 THEN 1 END) AS [Total]
FROM (
SELECT
a.SiteCode,
a.VisitCode,
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 VisitCode 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
I am getting an Error Invalid Column name 'VisitCode' but Col exists, seems like I am missing something.
SUM(CASE WHEN VisitCode = 1 THEN 1 END) AS [PM],
COUNT(CASE WHEN VisitCode = 1 THEN 1 END) AS [PMCount],
SUM(CASE WHEN VisitCode = 2 THEN 1 END) AS [CM],
COUNT(CASE WHEN VisitCode = 2 THEN 1 END) AS [CMCount],
SUM(CASE WHEN VisitCode = 1 THEN 1 END) + SUM(CASE WHEN VisitCode = 2 THEN 1 END) AS [Total]
2条答案
按热度按时间9fkzdhlc1#
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...
I also removed your a, b, c aliases, and used letters that mean something, and improved the WHERE clause to be more robust.
xdnvmnnf2#
To fix this error, you can include the "VisitCode" column in the SELECT statement of the inner query, like this: