使用UNION和GROUP BY

我继承了一些复杂的SQL Server代码,我正在尝试将其用于新数据。我想知道的是,如果有人在医院,开了处方,或者在2017年接种了疫苗;根据他们的年龄 unique_id . 我正在查看各种表格,以找到他们与卫生系统的联系,提取2017年的相关事件,然后从中添加人口统计信息 table.dems table.geo .

我要每人一行 ,显示1,如果他们与医院有接触,或得到处方或免疫接种;然后显示他们的人口统计信息。

摘要代码为:

WITH CORE_POP_17 AS
( SELECT unique_id,
         year_num,
         MAX(aaa) as aaa,
         MAX(ccc) as ccc,
         MAX(bbb) as bbb
FROM (
      SELECT unique_id,
             year(event_date) as year_num,
             1 as aaa, 
             0 as ccc, 
             0 as bbb
      FROM table.hosps
      WHERE year_num IN (2017)

      
      UNION

       SELECT unique_id,
             year(event_date) as year_num,
             0 as aaa, 
             1 as ccc, 
             0 as bbb
      FROM table.pharms
      WHERE year_num IN (2017)
      
      UNION

       SELECT unique_id,
             year(event_date) as year_num,
             0 as aaa, 
             0 as ccc, 
             1 as bbb
      FROM table.imms
      WHERE year_num IN (2017)

     )
GROUP BY unique_id
)
SELECT
unique_id, 
aaa,
bbb,
ccc,
dems.rrr,
geo.ppp

FROM CORE_POP_17 CORE_POP
LEFT JOIN table.dems dems
ON dems.unique_id = CORE_POP.unique_id
LEFT JOIN table.geo geo
on geo.unique_ID = CORE_POP.unique_id

GROUP BY命令引发错误

任何有助于这项工作非常感谢。谢谢。