Group By Date With Empty Groups

Question

I have a loginAudit table and I am trying to get a count for all logins for each day. What I'd like to do is have days where there are no logins return their day and a login count of 0. Currently no row is returned for days with no logins.

Could be this isn't possible and have to fill in empty groups in the app after query results returned?

  SELECT DATEADD(day, DATEDIFF(day,0,LoginAudit.LoginDateTime), 0) as LoginDate,  
         COUNT(DISTINCT LoginAudit.LoginAuditID) AS Logins  
    FROM LoginAudit 
GROUP BY DATEADD(day, DATEDIFF(day,0,LoginAudit.LoginDateTime), 0)
ORDER BY 1

Answer 1

What DBMS are you using?

If Oracle, you might try selecting your dates in a sub-query, like so:

SELECT TRUNC(SYSDATE) + 1 - LEVEL AS today,
       TRUNC(SYSDATE) + 2 - LEVEL AS tomorrow
FROM DUAL
CONNECT BY LEVEL <= 30 /* Last 30 days */

Then you might do:

SELECT today as LoginDate,  
       COUNT(DISTINCT LoginAudit.LoginAuditID) AS Logins  
FROM (
       SELECT TRUNC(SYSDATE) + 1 - LEVEL AS today,
              TRUNC(SYSDATE) + 2 - LEVEL AS tomorrow
       FROM DUAL
       CONNECT BY LEVEL <= 30 /* Last 30 days */
     ),
     LoginAudit 
WHERE LoginAudit.LoginDateTime BETWEEN today AND tomorrow
GROUP BY today
ORDER BY 1

Answer 2

I thought I'd searched for solutions fairly thoroughly, but of course right after posting my question I found this link:

http://stackoverflow.com/questions/329477/sql-group-by-day-show-orders-for-each-day

dooh!

Answer 3

Essentially what you're asking is to join your table to a "table" of dates. The date table would have no gaps and you would group on the date value. So how to create a table of dates?

In SQL for Smarties, it's suggested that you keep a table of integers around for cases when you need a gapless sequence to join to. Then you can select whatever sequence you need by joining your table to it.

So if you had an integer table with values going as many days back from NOW() as required you might do the following:

SELECT DATE_SUB(CURDATE(), INTERVAL i.intvalue DAY) AS thedate, 
       COUNT(DISTINCT LoginAudit.LoginAuditID) AS logins
FROM i LEFT JOIN dual ON (DATE_SUB(NOW(), INTERVAL i.intvalue DAY)= day)
GROUP BY DATE_SUB(CURDATE(), INTERVAL i.intvalue DAY)
ORDER BY i DESC;