SQL Server COUNT() 似乎忽略了 WHERE

我正在嘗試設置一個 SQL Server 查詢，其中列出了在會議活動期間報名參加旅行的人數，然後顯示總和。這是我即將接近的 SQL 程式碼，但它忽略了總計（但不是單個總計）的 WHERE 子句：

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

這是它正在生產的東西：

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

（Tour1 計數 22 是正確的，因為有一個 RegistrationType 取消。但是，83 的 TOTAL 應該是 82。）

另外，我得到一個初始行，我猜基於對 NULL 的計數，它顯示為 0。有什麼方法可以防止這種情況發生嗎？

請幫我糾正這個。謝謝你。

您需要在聯合選擇中添加相同的 where 子句。

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')

你應該避免做COUNT兩次。

;WITH Counts AS
(
   SELECT     SiteTour, COUNT(SiteTour) AS count
   FROM       tblConference
   WHERE      RegistrationType <> 'cancellation' AND SiteTour IS NOT NULL
   GROUP BY   SiteTour
),
Totals AS
(
   SELECT
       C.SiteTour,
       C.count
   FROM
       Counts AS C
   UNION ALL
   SELECT     
       'TOTAL' AS SiteTour, 
       SUM(C.count) AS count
   FROM
       Counts AS C
)
SELECT
   T.SiteTour,
   T.Count
FROM
   Totals AS T
ORDER BY
   CASE 
       WHEN T.SiteTour <> 'TOTAL' THEN 1
       ELSE 2 END,
   T.SiteTour

這樣你所有的過濾器也只寫一次。

引用自：https://dba.stackexchange.com/questions/210865