Group by date with empty groups
I have a loginAudit table and I am trying to get the score for all logins for each day. What I would like to do is have days where there are no logins returning their day and the number of logins is 0. Currently, no row is returned for several days without logins.
Maybe this is not possible and need to populate empty groups in the application after returning the query results?
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
source to share
Essentially, you are asking to join your table in a "table" of dates. There will be no spaces in the date table and you will be grouping the date value. So how do you create a date table?
In SQL for Smarties , he suggested keeping a table of integers for cases where you need a brushless sequence to join. Then you can select any sequence you need by attaching a table to it.
So, if you had a whole table with values going as many days ago with NOW () as needed, you can 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 a whole table
create table i(i integer not null primary key);
insert into i values (0),(1),(2) ... (9);
if i want 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 want 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;
source to share
I thought I was looking for solutions thoroughly enough, but of course, right after posting my question, I found this link:
SQL group by day, show orders for every day
DooH!
source to share
What DBMS do you use?
If Oracle, you can try to select your dates in a subquery like:
SELECT TRUNC(SYSDATE) + 1 - LEVEL AS today,
TRUNC(SYSDATE) + 2 - LEVEL AS tomorrow
FROM DUAL
CONNECT BY LEVEL <= 30 /* Last 30 days */
Then you can 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
source to share