NULL
is already ignored so you can use NULLIF
to turn 0
to NULL
. Also you don’t need DISTINCT
and your WHERE
on ActualTime
is not sargable.
SELECT AVG(cast(NULLIF(a.SecurityW, 0) AS BIGINT)) AS Average1,
AVG(cast(NULLIF(a.TransferW, 0) AS BIGINT)) AS Average2,
AVG(cast(NULLIF(a.StaffW, 0) AS BIGINT)) AS Average3
FROM Table1 a
WHERE a.ActualTime >= '20130401'
AND a.ActualTime < '20130501'
PS I have no idea what Table2 b
is in the original query for as there is no join condition for it so have omitted it from my answer.