Get the average attendance?

Question

I am trying to develop a subquery to get the average attendance from two tables along with full details

Answer 1

select EVENTID ,
EVENTNAME ,
STARTDATETIME ,
ENDDATETIME ,
CONCERT_CONCERTID ,
VENUE_VENUEID,COUNT(TIMEOFARRIVAL),AVG(TIMEOFARRIVAL) 
FROM EVENT INNER JOIN BOOKING ON BOOKING.EVENT_EVENTID = EVENT.EVENTID;

There is an error inconsistent datatypes expected number got timestamp

Answer 2

Your first step is to get the number of attendees per event:

SELECT e.eventid, COUNT(b.customer_customerid) AS customer_cnt
  FROM event e LEFT JOIN booking b
    ON e.eventid = b.event_eventid
 GROUP BY e.eventid;

Now I use a LEFT JOIN above because an event could conceivably have zero attendance and those should be counted as well!

We can get the average using a window function (what Oracle calls an analytic function):

SELECT eventid, attendance, AVG(attendance) OVER ( ) AS avg_attendance
  FROM (
    SELECT e.eventid, COUNT(b.customer_customerid) AS attendance
      FROM event e LEFT JOIN booking b
        ON e.eventid = b.event_eventid
     GROUP BY e.eventid
);

Now we can get all events with below-average attendance:

SELECT eventid, attendance FROM (
    SELECT eventid, attendance, AVG(attendance) OVER ( ) AS avg_attendance
      FROM (
        SELECT e.eventid, COUNT(b.customer_customerid) AS attendance
          FROM event e LEFT JOIN booking b
            ON e.eventid = b.event_eventid
         GROUP BY e.eventid
    )
) WHERE attendance < avg_attendance;

The above will give you a list of eventids - you can get the rest of the columns you need by querying the event table with this list.

Edited based on comments - poster would like to determine the events for each concert with below average attendance for that concert.

SELECT concertid, eventid, attendance, avg_attendance FROM (
    SELECT concertid, eventid, attendance, AVG(attendance) OVER ( PARTITION BY concertid ) AS avg_attendance
      FROM (
        SELECT c.concertid, e.eventid, COUNT(b.timeofarrival) AS attendance
          FROM concert c INNER JOIN event e
            ON c.concertid = e.concert_concertid
          LEFT JOIN booking b
            ON e.eventid = b.event_eventid
         GROUP BY e.eventid
    )
) WHERE attendance < avg_attendance;