How To Use avg

Oracle – How To Use avg(count(*)) on Having Clause Or Ehere clause?

 

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>

LEAVE A REPLY

Please enter your comment!
Please enter your name here