CODEWITHSUNDEEP
oracle-databaseoracle12c
Nick
Nick

Reputation: 53

How to fill out grouped data by day?

From events table, I get grouped events count by day and for this I use

SELECT event_date, COUNT(event_id) event_count FROM events
WHERE event_date >= TRUNC(SYSDATE-1, 'DD')
GROUP BY event_date
ORDER BY event_date

My problem here is that this returns only these days, where are some events

2017-04-03 , 4
2017-04-05 , 2

but I need get all consecutive days from Yesterday to next 30 day, and fill out this days from my grouped events data, like this

2017-03-31 ,
2017-04-01 ,
2017-04-02 ,
2017-04-03 , 4
2017-04-04 ,
2017-04-05 , 2
2017-04-06 ,
... Next 30 days (with events count when events exists in my table) ...

How to do that? Thank you for any help

Upvotes: 0

Views: 35

Answers (1)

Oto Shavadze
Oto Shavadze

Reputation: 42763

So, you can generate next 30 days from yesterday (see first subquery) and then simply left join it to your existing query.

Try this:

select  all_days.days  ,  certain_days.event_count from (
    select  TRUNC( sysdate + level - 2, 'DD') as days from DUAL connect by level <= 30
) all_days
left join (
    SELECT event_date, COUNT(event_id) event_count FROM events
    WHERE event_date >= TRUNC(SYSDATE-1, 'DD')
    GROUP BY event_date
) certain_days
on   all_days.days  =  certain_days.event_date
order by  all_days.days

Upvotes: 2

Related Questions