Oracle - How to use avg(count(*)) on having clause or where clause?

Question

I have data like this:

CONCERT_ID   EVENT_ID ATTENDANCE AVG_ATTENDANCE_EACH_CONCERT
---------- ---------- ---------- ---------------------------
         1          1          1                         1,5
         1          2          2                         1,5
         3          5          2                           2
         3          6          2                           2
         5         11          4                           2
         5         12          1                           2
         5         13          1                           2

Thats from this query:

select concert_id, event_id, count(customer_id) attendance,
avg(count(*)) over (partition by concert_id) avg_attendance_each_concert
from booking
group by concert_id, event_id
order by event_id;

How to make a limitation on that query. What i want to make is

If the attendance is below average attendance show result

I already tried avg(count(*)) over (partition by concert_id) to having clause but gave me an error group function is too deep

Answer 1

It's easy to get the desired results by applying just one nesting :

select * from
(
 select concert_id, event_id, count(customer_id) attendance, 
        avg(count(*)) over (partition by concert_id) avg_attendance_each_concert
   from booking
  group by concert_id, event_id
  order by event_id
)
where attendance < avg_attendance_each_concert

D e M o

Answer 2

Include an "intermediate" table, a query which returned the correct result in your last question. Then select values - which satisfy a new condition - from it.

SQL> with booking (concert_id, event_id, customer_id) as
  2  (select 1, 1, 10 from dual union
  3   select 1, 2, 10 from dual union
  4   select 1, 2, 20 from dual union
  5   --
  6   select 3, 5, 10 from dual union
  7   select 3, 5, 20 from dual union
  8   select 3, 6, 30 from dual union
  9   select 3, 6, 40 from dual union
 10   --
 11   select 5, 11, 10 from dual union
 12   select 5, 11, 20 from dual union
 13   select 5, 11, 30 from dual union
 14   select 5, 11, 40 from dual union
 15   select 5, 12, 50 from dual union
 16   select 5, 13, 60 from dual
 17  ),
 18  inter as
 19  (select concert_id, event_id, count(customer_id) attendance,
 20          avg(count(*)) over (partition by concert_id) avg_attendance_each_concert
 21   from booking
 22   group by concert_id, event_id
 23  )
 24  select concert_id, event_id, attendance, avg_attendance_each_concert
 25  from inter
 26  where attendance < avg_attendance_Each_concert
 27  order by event_id;

CONCERT_ID   EVENT_ID ATTENDANCE AVG_ATTENDANCE_EACH_CONCERT
---------- ---------- ---------- ---------------------------
         1          1          1                         1,5
         5         12          1                           2
         5         13          1                           2

SQL>