count aggregate between three date ranges 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 &gt; 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
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 &gt;= (now() - interval 24 hour) then nc.id else null end)) as day0, 
       count(distinct(case when nc.created &lt; (now() - interval 24 hour) 
                            and nc.created &gt;= (now() - interval 48 hour) then nc.id else null end)) as day1, 
       count(distinct(case when nc.created &lt; (now() - interval 48 hour) 
                            and nc.created &gt;= (now() - interval 72 hour) then nc.id else null end)) as day2
from   nc
where  nc.flowstepname like "1%"
and    nc.created &gt; NOW() - INTERVAL 72 HOUR
group by nc.flowstepname ASC;




flowstepname | day0 | day1 | day2
:----------- | ---: | ---: | ---:
1152         |    2 |    2 |    3



dbfiddle here