Oracle DB - Single query non-linear time grouping report (1 day, 1 week, 1 month, 3 months, total)

Question

I'm trying to build a report that would essentially be a single query that combines the output of these queries:

select count(*) from my_table where createddate >= CURRENT_DATE - 1;
select count(*) from my_table where createddate >= CURRENT_DATE - 7;
select count(*) from my_table where createddate >= CURRENT_DATE - 30;
select count(*) from my_table where createddate >= CURRENT_DATE - 90;
select count(*) from my_table;

Such that the output would be similar to:

Time_Period   Count
===================
Yesterday        5
Last Week       20
Last Month      50
Last 90 Days   100
Total         5000

I've had success building several linear time series queries (by day, by week, by month, etc). But no luck in being able to build a non-linear reporting query.

Answer 1

You can put the results in separate columns easily:

select sum(case when createddate >= CURRENT_DATE - 1 then 1 else 0 end) as yesterday,
       sum(case when createddate >= CURRENT_DATE - 7 then 1 else 0 end) as last_week,
       sum(case when createddate >= CURRENT_DATE - 30 then 1 else 0 end) as last_month,
       sum(case when createddate >= CURRENT_DATE - 90 then 1 else 0 end) as last_90days,
       count(*) as total
from my_table;

If you want separate rows, you can unpivot the above, or just use union all:

select 'Yesterday' as which, count(*) from my_table where createddate >= CURRENT_DATE - 1
union all
select 'Last week', count(*) from my_table where createddate >= CURRENT_DATE - 7
union all
select 'Last month', count(*) from my_table where createddate >= CURRENT_DATE - 30
union all
select 'Last 90 days', count(*) from my_table where createddate >= CURRENT_DATE - 90
union all
select 'Total', count(*) from my_table;