Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.


1 Answers

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.