当COUNT（*）为NULL时在GROUP BY中返回0 [英] Return 0 in GROUP BY when COUNT(*) is NULL

问题描述

这是我的原始查询：

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

基于，我尝试了以下操作，但仍然收到了差距，但最重要的是结果是错误的（数字高出很多）：

Based on this question, I tried the following and still received the gaps but on top of that the results were wrong (the numbers were MUCH higher):

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

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

How can I get my results to look like this?

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

I' ve还检查了其他几个问题，但它们都涉及联接或其他因素，而不属于我的情况。

I've checked a few other questions but they all involve joins or other factors not in my scenario.

UPDATE

根据评论，我尝试了外部联接。该迭代最终成功运行，但是结果有点倒退...

Based on comments, I've attempted an OUTER JOIN. This iteration finally ran successfully, but the results were a bit backwards...

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 包括es缺少日期。

I verified that aggregate b includes the missing dates.

推荐答案

所以我将汇总从编辑内容切换到原始帖子，现在可以正常工作了：

So I flipped the aggregates from the edit to my original post and now it's working:

查询

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的日期。

Note that I had to replace COUNT(*) with COUNT(EventType) so it wouldn't count the date from the aggregate which was resulting in a 1.

这篇关于当COUNT（*）为NULL时在GROUP BY中返回0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！