sql - 当 COUNT(*) 为 NULL 时，在 GROUP BY 中返回 0

Question

这是我的原始查询:

SELECT
    CAST(IndexedDate as varchar),
    COUNT(*) AS Logins
FROM
    Table
WHERE
    EventType = 'Login'
AND IndexedDate > DATEADD(mm, -1, GETDATE())
GROUP BY
    IndexedDate
ORDER BY
    IndexedDate DESC

这会留下空白，例如:

2016-09-13    41
2016-09-12    31
2016-09-09    15
2016-09-08    36

基于 this question ，我尝试了以下方法，但仍然收到了差距，但最重要的是结果是错误的(数字要高得多):

SELECT
    CAST(IndexedDate as varchar),
    SUM(Case When COUNT(*) Is Null Then 0 Else COUNT(*)  End) AS Logins
FROM
...

我怎样才能让我的结果看起来像这样？

2016-09-13    41
2016-09-12    31
2016-09-11    0
2016-09-10    0
2016-09-09    15
2016-09-08    36

我检查了其他几个问题，但它们都涉及连接或其他不在我的场景中的因素。

---

更新

根据评论，我尝试了 OUTER JOIN。这次迭代终于跑成功了，但是结果有点倒退……

SELECT
        CAST(a.IndexedDate as varchar) as dt,
        COUNT(*) AS Logins
FROM 
        (
        SELECT *
        FROM Table
        WHERE IndexedDate > DATEADD(mm, -1, GETDATE())
        AND EventType = 'Login'
        ) a
FULL OUTER JOIN (
        SELECT DISTINCT(IndexedDate)
        FROM Table
        WHERE IndexedDate > DATEADD(mm, -1, GETDATE())
        ) b
ON 
        a.IndexedDate = b.IndexedDate
GROUP BY
        b.IndexedDate
ORDER BY
        b.IndexedDate DESC

结果:

2016-09-13    41
2016-09-12    31
(null)    1
(null)    1
2016-09-09    15
2016-09-08    36

我验证了聚合 b 包括缺失的日期。

Answer 1

所以我将聚合从编辑翻转到我的原始帖子，现在它正在工作:

查询

SELECT
        CAST(a.IndexedDate as varchar) as dt,
        COUNT(EventType) AS Logins
FROM 
        (
        SELECT DISTINCT(IndexedDate)
        FROM Table
        WHERE IndexedDate > DATEADD(mm, -1, GETDATE())
        ) a
FULL OUTER JOIN (
        SELECT *
        FROM Table
        WHERE IndexedDate > DATEADD(mm, -1, GETDATE())
        AND EventType = 'Login'
        ) b
ON 
        a.IndexedDate = b.IndexedDate
GROUP BY
        a.IndexedDate
ORDER BY
        a.IndexedDate DESC

结果

2016-09-13    41
2016-09-12    31
2016-09-11    0
2016-09-10    0
2016-09-09    15
2016-09-08    36

请注意，我必须将 COUNT(*) 替换为 COUNT(EventType)，这样它就不会计算结果为 1 的聚合日期。