I need to write a query where I need to first count the tickets sold for spectacles in a theater, then calculate the average number of tickets sold in each theater and to be round it to two decimal place and these values must to be sort by the number of tickets sold. In principle, I want to find the average number of tickets sold for each theater. For this query I must use AVG() and ROUND(). I tried so many different options, but I have different errors. When I tried to run this, this error appeared :
SQL Error: ORA-00937: not a single-group group function
SELECT
t.id_theater
, s.id_spectacle
, ROUND((AVG(COUNT(tickets.id_ticket))), 2) average_tickets_theatre
FROM
tickets tick
, theater t
, spectacles s
WHERE
tick.id_spectacle = s.id_spectacle
AND t.id_theater = s.id_theater
GROUP BY
t.id_theater
, s.id_spectacle
ORDER BY
average_tickets_theatre
I tried to add the HAVING clause, but another error appeared:
SQL Error: ORA-00933: SQL command not properly ended
I'm sure that the problem consists in the fact that I'm trying to use COUNT with ROUND() and AVG(), but I really don't know what do to at this point and would appreciate any help.
joins
instead of comma separated