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

dnagirl · Accepted Answer · 2012-05-07T16:39:56.283

4

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;

ETA, for mysql:

//create an integer table

create table i(i integer not null primary key);
insert into i values (0),(1),(2) ... (9);

if I need 0-99 consecutive numbers:

SELECT 10*t.i + u.i AS number
  FROM i AS u
CROSS JOIN 
  i AS t
ORDER BY number;

if I need consecutive dates:

SELECT date_sub(curdate(), interval (10*t.i + u.i) DAY) as thedate 
  FROM i AS u
CROSS JOIN 
  i AS t
ORDER BY thedate;

edited May 07 '12 at 16:39

answered Sep 25 '09 at 16:13

dnagirl

20,196
13
80
123

@joon: yes. But trying it is the easiest way to find out. – dnagirl May 07 '12 at 13:42
I Did, and couldn't get it to work... Mysql seems to fail on the term "DUAL". (SELECT DATE_SUB(CURDATE(), INTERVAL ints.id DAY) AS thedate, COUNT(DISTINCT winners.id) AS winners FROM ints LEFT JOIN dual ON (DATE_SUB(NOW(), INTERVAL ints.id DAY)= day) GROUP BY DATE_SUB(CURDATE(), INTERVAL ints.id DAY) ORDER BY ints DESC;) – joon May 07 '12 at 15:34
@joon: sorry about that. The post is old and I almost always use MySQL. – dnagirl May 07 '12 at 16:29
Do you have any idea how I can make it work (the query I posted above)? Otherwise I'll make it into a new question :) – joon May 07 '12 at 16:30
@joon: added some MySQL to my answer to get you started. – dnagirl May 07 '12 at 16:40

score 3 · Answer 2 · edited May 23 '17 at 10:27

3

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

SQL group by day, show orders for each day

dooh!

edited May 23 '17 at 10:27

Community

1
1

answered Sep 25 '09 at 15:57

Binz

580
4
9

score 0 · Answer 3 · answered Sep 25 '09 at 15:54

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

Unfortunately using MS SQL Server 2005. Thanks though! – Binz Sep 25 '09 at 15:57 — Binz, Sep 25 '09 at 15:57

Group By Date With Empty Groups

3 Answers3