Get yearly average of per id

This code gets the number of ids that that show up for each month with in the year.

WITH t1 as (SELECT * FROM daily WHERE EVENT_DATE BETWEEN DATE '2019-01-01' and DATE '2019-12-31'),
     
SELECT ID, COUNT(ID) AS monthly_count, TO_CHAR(EVENT_DATE, 'YYYY-MM') as mon_yy
FROM t1 GROUP BY ID, TO_CHAR(EVENT_DATE, 'YYYY-MM')
      

This is the result i get:

1 |2019-01 |152
1 |2019-02 |127
1 |2019-03 |186
1 |2019-04 |47
1 |2019-05 |31
1 |2019-06 |46
1 |2019-07 |34
1 |2019-08 |22
1 |2019-09 |17
1 |2019-10 |22
1 |2019-11 |12
1 |2019-12 |16
2 |2019-01 |84930842
2 |2019-02 |69685158
2 |2019-03 |109122032
2 |2019-04 |114767006
2 |2019-05 |116766004
2 |2019-06 |197809839
2 |2019-07 |205065236
2 |2019-08 |383143170
2 |2019-09 |244118916
2 |2019-10 |210451973
2 |2019-11 |142372447
2 |2019-12 |63189538
3 |2019-01 |4361080
3 |2019-02 |5679732
3 |2019-03 |10101952
         

I want to get the yearly average of each ID, and the overall count. The results will look like this:

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

Answer

You can add another level of aggregation:

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 < '2020-01-01'
    group by id, trunc(event_date, 'month')
) t
group by id, to_char(event_month, 'yyyy')

Notes:

• you don’t actually need a CTE to pre-filter the data: this can be done directly in the first aggregation query

• I changed the filtering logic to use half-open intervals; if your dates have time components that are other than '00:00:00', then this avoids filtering out the last day of the month (and if not, it doesn’t hurt either)