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

Question

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.

Outer join with a calendar table (holding all possible dates). — jarlh, Sep 16 '16 at 13:38
ahhh, makes sense. I was hoping for an easier way but I guess you can't get something from nothing. — , Sep 16 '16 at 13:39
Second @jarlh's answer. This is really the only reasonable way to do what you want to do. — WillardSolutions, Sep 16 '16 at 13:39
To follow on the comment by @jarlh , SQL queries are great for taking existing information and transforming it it into something you want. But in your case, if date data really be missing, then calendar tables are a good way to bring that data in. — Tim Biegeleisen, Sep 16 '16 at 13:40
You actually create a list of dates on the fly using a technique like so: http://stackoverflow.com/questions/11141507/how-to-generate-a-range-of-dates-in-sql-server in SQL Server. That table `n` can be joined with your table on date to get count(*) where needed. — zedfoxus, Sep 16 '16 at 13:40
Well, I'm giving it a go, but the query is already taking longer than it should... I'll keep playing with it and if nobody has posted an actual answer I'll post when I get it working. — , Sep 16 '16 at 13:55
@KamilG.: it makes a **big** difference which DBMS is being used (and if you think it doesn't, you shouldn't have added the `sql-server` tag) — , Sep 16 '16 at 14:31
Well I tried my hand at an `OUTER JOIN` with limited luck. Please see update — , Sep 16 '16 at 15:06

score 2 · Accepted Answer · answered Sep 16 '16 at 15:12

2

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.

answered Sep 16 '16 at 15:12

No, the `1` comes from the fact that when you `full outer join` to b, every row in b will have a row in your resultset, meaning the *count of rows* for that date will really be 1, even if you don't have any data from table `a` for that row. You need to replace your `count(*)` expression in your `select` with `case when a.IndexedDate is null then 0 else count(*) end` to correctly assign a `0` for those rows. – SlimsGhost Sep 16 '16 at 15:17
I was about 10 seconds ahead of you! Thanks though! – Sep 16 '16 at 15:18
Scott do you have an Event for each IndexedDate, even if it is not a Login EventType? If you had no events on a given day in your table, wouldn't this omit that date? – EoinS Sep 16 '16 at 15:27
Yes, but as mentioned in the original question I did validate that all the dates are there, plus this table is MASSIVE and there are tens of thousands of logged events per day. If a date is missing, there's something terribly wrong :) – Sep 16 '16 at 18:31

EoinS · Answer 2 · 2016-09-16T15:28:33.523

This works (in SQL Server)

declare @mindt date = (select min(IndexedDate ) from p);
declare @dtrange int = DATEDIFF(day,@mindt,(select max(IndexedDate ) from p));

with MyCte AS
    (select   MyCounter = 0
     UNION ALL
     SELECT   MyCounter + 1
     FROM     MyCte
     where    MyCounter < @dtrange)
select coalesce(IndexedDate , dateadd(d, mycounter, @mindt)) IndexedDate
, count(IndexedDate)
from   MyCte 
left join p
  on dateadd(d,mycounter,@mindt) = p.IndexedDate 
group by coalesce(IndexedDate , dateadd(d, mycounter, @mindt))
option (maxrecursion 0);

We basically need two main figures, the starting date and the date range.

The we build a quick counter for the number of days in the date range.

Then we select each slot in the date range and assign a date and a value, if there are none we create a date with DateAdd and assign 0 as the value.

Here is a functional example

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

2 Answers2

Linked