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.