0

I have a mysql query where I want 3 columns of data. day0 to be between now() and - INTERVAL 24 hours day1 between end of day0 - 48 hours and day2 between end of day1 and 72 hours

This is what I have so far.

select distinct nc.flowstepname, 
       count(distinct(nc.id)) as day0, 
       count(distinct(nc.id)) as day1, 
       count(distinct(nc.id)) as day2
from sparq.nc nc
where nc.flowstepname like "1%"
    and nc.created > NOW() - INTERVAL 72 HOUR
group by nc.flowstepname ASC;

Like this:

select distinct nc.flowstepname, 
       (select count(distinct(nc.id)) 
        from sparq.nc nc 
        where nc.flowstepname like "1%" 
        and nc.created = now() - interval 24 hour) as day0, 
       (select count(distinct(nc.id)) 
        from sparq.nc nc 
        where nc.flowstepname like "1%" 
        and nc.created = interval 24 hour - interval 48 hour) as day1, 
       (select count(distinct(nc.id)) 
        from sparq.nc nc 
        where nc.flowstepname like "1%" 
        and nc.created = interval 48 hour - interval 72 hour) as day2 
from sparq.nc nc 
group by nc.flowstepname ASC
  • Please add some sample data and the desired result. – McNets Apr 3 '18 at 17:07
  • like this select distinct nc.flowstepname, (select count(distinct(nc.id)) from sparq.nc nc where nc.flowstepname like "1%" and nc.created = now() - interval 24 hour) as day0, (select count(distinct(nc.id)) from sparq.nc nc where nc.flowstepname like "1%" and nc.created = interval 24 hour - interval 48 hour) as day1, (select count(distinct(nc.id)) from sparq.nc nc where nc.flowstepname like "1%" and nc.created = interval 48 hour - interval 72 hour) as day2 from sparq.nc nc group by nc.flowstepname ASC; – Stevie Uyeda Apr 3 '18 at 17:23
1

You could use COUNT(DISCTINCT(CASE ... WHEN

I've set up next example:

CREATE TABLE nc (id int, flowstepname varchar(20), created datetime);

INSERT INTO nc VALUES
(1,  '1152', NOW() - interval 20 hour),
(1,  '1152', NOW() - interval 21 hour),
(2,  '1152', NOW() - interval 22 hour),
(1,  '1152', NOW() - interval 31 hour),
(1,  '1152', NOW() - interval 32 hour),
(1,  '1152', NOW() - interval 34 hour),
(2,  '3154', NOW() - interval 35 hour),
(2,  '1152', NOW() - interval 40 hour),
(1,  '1152', NOW() - interval 49 hour),
(1,  '1152', NOW() - interval 52 hour),
(2,  '4152', NOW() - interval 60 hour),
(3,  '1152', NOW() - interval 65 hour),
(4,  '1152', NOW() - interval 66 hour);

select nc.flowstepname, 
       count(distinct(case when nc.created >= (now() - interval 24 hour) then nc.id else null end)) as day0, 
       count(distinct(case when nc.created < (now() - interval 24 hour) 
                            and nc.created >= (now() - interval 48 hour) then nc.id else null end)) as day1, 
       count(distinct(case when nc.created < (now() - interval 48 hour) 
                            and nc.created >= (now() - interval 72 hour) then nc.id else null end)) as day2
from   nc
where  nc.flowstepname like "1%"
and    nc.created > NOW() - INTERVAL 72 HOUR
group by nc.flowstepname ASC;
flowstepname | day0 | day1 | day2
:----------- | ---: | ---: | ---:
1152         |    2 |    2 |    3

dbfiddle here

|improve this answer|||||
  • Thank you, I forgot to mention I'm set in a read only option so I cannot use Create Table though – Stevie Uyeda Apr 4 '18 at 2:04
  • but the rest should work fine – Stevie Uyeda Apr 4 '18 at 2:05
  • I've created a table just to show you how it works. – McNets Apr 4 '18 at 6:52
  • day1 can be simplified to SUM(nc.created < (now() - interval 24 hour) and nc.created >= (now() - interval 48 hour)). (etc) – Rick James Apr 27 '18 at 3:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.