CODEWITHSUNDEEP
sqloracle-databasedatetimecount
user872009
user872009

Reputation: 438

Find the average of ids in a month

I can calculate the number of ids in a month and then sum it up over 12 months. I also get the average using this code.

select id, to_char(event_month, 'yyyy') event_year, sum(cnt) overall_count, avg(cnt) average_count
from (
    select id, trunc(event_date, 'month') event_month, count(*) cnt
    from daily 
    where event_date >= date '2019-01-01' and event_date < '2019-01-31'
    group by id, trunc(event_date, 'month')
) t
group by id, to_char(event_month, 'yyyy')

The results looks something like this:

ID| YEAR | OVER_ALL_COUNT| AVG
 1| 2019 | 712           | 59.33
 2| 2019 | 20936849      | 161185684.6
 3| 2019 | 14255773      | 2177532.2

However, I want to modify this to get the over all id counts for a month instead and the average of the id counts per month. Desired result is:

ID| MONTH | OVER_ALL_COUNT| AVG
 1| Jan   | 152            | 10.3
 2| Jan   | 15000          | 1611
 3| Jan   | 14255          | 2177
 1| Feb   | 4300           | 113
 2| Feb   | 9700           | 782
 3| Feb   | 1900           | 97

where January has 152 id counts over all for id=1, and the average id count per day is 10.3. For id=2, the january count is 15000 and the average id=2 count for jan is 1611.

Upvotes: 0

Views: 519

Answers (2)

EJ Egyed
EJ Egyed

Reputation: 6084

You just need to change the truncating on your subquery to truncate by day instead of by month, then truncate the outer query by month instead of year.

select id, to_char(event_day, 'Mon') event_month, sum(cnt) overall_count, avg(cnt) average_count
from (
    select id, trunc(event_date) event_day, count(*) cnt
    from daily 
    where event_date >= date '2019-01-01' and event_date < date '2019-01-31'
    group by id, trunc(event_date)
) t
group by id, to_char(event_month, 'Mon')

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269793

This answers the original version of the question.

You can use last_day():

select id, to_char(event_month, 'yyyy') event_year, sum(cnt) as overall_count,
       avg(cnt) as average_count,
       extract(day from last_day(min(event_month)) as days_in_month,
       sum(cnt) / extract(day from last_day(min(event_month)) as avg_days_in_month
from (select id, trunc(event_date, 'month') as event_month, count(*) as cnt
      from daily 
      where event_date >= date '2019-01-01' and
            event_date < date '2020-01-01'
      group by id, trunc(event_date, 'month')
     ) t
group by id, to_char(event_month, 'yyyy')

Upvotes: 0

Related Questions