如何从关联其他表的XREF表分组数据中获取正确的事件注册与出席计数？

要实现需求中的查询，需调整表连接方式并使用条件聚合分别统计两类人员，最终SQL语句如下：

SELECT
    e.EventName,
    e.MaxAttendees,
    COUNT(xpe.ID) AS #Registered,
    SUM(CASE WHEN p.DateCanceled IS NULL THEN 1 ELSE 0 END) AS #Attending
FROM [Event] AS e
LEFT JOIN XPersonEvent AS xpe ON xpe.EventID = e.EventID
LEFT JOIN Person AS p ON p.PersonID = xpe.PersonID
GROUP BY e.EventName, e.MaxAttendees
ORDER BY e.EventName;

关键调整说明： #

• 显示所有事件：用LEFT JOIN从Event表出发关联另外两张表，确保无注册记录的事件（如Boston）也能被保留，无关联记录时计数自动为0。
• #Registered 统计：通过COUNT(xpe.ID)统计所有注册记录，无论用户是否取消报名——LEFT JOIN后无注册的xpe.ID为NULL，COUNT会忽略NULL值，返回0。
• #Attending 统计：用CASE语句判断用户是否未取消（DateCanceled IS NULL），符合条件则计1，否则计0，再通过SUM汇总得到实际出席人数。
• 移除过滤条件：删除原查询中WHERE p.DateCanceled IS NULL，避免过滤掉已取消的注册记录，同时保证所有事件都能被保留。

执行上述语句后，将得到目标报告结果：

EventName     MaxAttendees #Registered #Attending
------------- ------------ ----------- -----------
Austin        55           3           2
Boston        65           0           0
Cleveland     75           1           1

内容的提问来源于stack exchange，提问作者Austin