SQL Server COUNT() seems to be ignoring WHERE I am trying to set up a SQL Server query that lists the count of people signed up for tours during a conference event and then displays the total sum. This is my SQL code that is coming close, but it is ignoring the WHERE clause for the grand total (but not the individual totals): 

SELECT     SiteTour, COUNT(SiteTour) AS count
FROM       tblConference
WHERE      (RegistrationType NOT LIKE 'cancellation')
GROUP BY   SiteTour
UNION ALL
SELECT     'TOTAL' AS SiteTour, COUNT(SiteTour) AS Expr1
FROM       tblConference AS tblConference_1


Here is what it is producing: 

         0
Tour1   22
Tour2   30
Tour3   30
----------
TOTAL   83


(Tour1 count of 22 is correct, because there is one RegistrationType cancellation. However, the TOTAL of 83 should be 82.)

Also, I am getting an initial row, I guess based on counting NULLs, that shows a 0. Is there some way prevent this?

Please help me correct this. Thank you.
You need to add the same where clause in your union select.

SELECT     SiteTour, COUNT(SiteTour) AS count
FROM       tblConference
WHERE      (RegistrationType NOT LIKE 'cancellation')
GROUP BY   SiteTour
UNION ALL
SELECT     'TOTAL' AS SiteTour, COUNT(tblConference_1.SiteTour) AS Expr1
FROM       tblConference AS tblConference_1
WHERE      (tblConference_1.RegistrationType NOT LIKE 'cancellation')