我有一个雇员表，我需要根据课程1和课程2获取从指定月份开始的过去12个月的雇员总数。
表格设计

+-----------+-------------+
| Field     | Type        |   
+-----------+-------------+    | 
| emp_name  | varchar(30) | 
| join_date | date        | 
| emp_id    | int(5)      | 
| c1_sub    | date        | 
| c1_expire | date        | 
| c2_sub    | date        | 
| c2_expire | date        | 
| activity  | varchar(30) | 
| group     | varchar(30) | 
+-----------+-------------+

我想显示下面的输出。c1_sub激活日期和c2_sub激活日期基于此，我想显示过去12个月的员工计数。

+-----------+-------------+-----------+
| Month     | single      |Dual       |   
+-----------+-------------+-----------+
| Dec 22    | 10          | 2         |
| Nov 22    | 8           | 4         |
| Oct 22    | 3           | 5         |
| ......    | .           | 
| ......    | .           | 
| ......    | .           | 
| Dec 21    | 5           | 4
| Nov 21    | 6           | 5
| Oct 21    | 7           | 6
| Sept 21   | 7           | 8
+-----------+-------------+------------|

我尝试了下面的查询，但是我没有得到课程1和课程2的学生人数。我不知道在哪里使用求和函数。

SELECT DATENAME(MM,[c1_sub]) AS Month
      , YEAR([c1_sub]) AS Year,
        sum(case when [activity] = 'Single' then 1 else 0 end) AS Single,
        sum(case when [activity] = 'Dual' then 1 else 0 end) AS Dual
    FROM [Employee]
    WHERE [Group Name] !='Test' AND
    [c1_sub] IS Not NULL AND [c2_sub] IS NULL AND 
    [c1_sub] BETWEEN DATEADD(MONTH, -12, GETDATE()) AND GETDATE() 
    GROUP BY YEAR([c1_sub]), DATENAME(MM,[c1_sub]) )

    UNION  ALL

        SELECT DATENAME(MM,[c2_sub]) AS Month
      , YEAR([c2_sub]) AS Year,
        sum(case when [activity] = 'Single' then 1 else 0 end) AS Single,
        sum(case when [activity] = 'Dual' then 1 else 0 end) AS Dual
    FROM [Employee]
    WHERE [Group Name] !='Test' AND
    [c2_sub] IS NOT NULL AND 
    [c2_sub] BETWEEN DATEADD(MONTH, -12, GETDATE()) AND GETDATE() 
    GROUP BY YEAR([c2_sub]), DATENAME(MM,[c2_sub]);

当我使用union all时，我得到的结果与预期的不同，

+-----------+-------------+-----------+
| Month     | single      |Dual       |   
+-----------+-------------+-----------+
| Dec 22    | 10          | 2         |
| Dec 22    | 2           | 3         |
| Nov 22    | 8           | 4         |
| Nov 22    | 2           | 5         |

| Oct 22    | 3           | 5         |
| ......    | .           | 
| ......    | .           | 
| ......    | .           | 
| Dec 21    | 5           | 4
| Nov 21    | 6           | 5
| Oct 21    | 7           | 6
| Sept 21   | 7           | 8
+-----------+-------------+------------|

但是，我得到的是分别激活过程1和激活计数2，而不是将这两个值相加。