sqlcountnullgroup-byintersystems-cache

Return 0 in GROUP BY when COUNT(*) is NULL


Here is my original query:

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

This would leave gaps, for example:

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 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

Results:

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

I verified that aggregate b includes the missing dates.


Solution

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

    Query

    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
    

    Results

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

    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.