0

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.

7
  • 1
    Sql-server is a different product than Oracle, unless your question regards both you should remove the tag that is wrong. Jan 5, 2021 at 23:42
  • 1
    can you please make a minimal reproducible example for your query also, you should change to proper joins instead of comma separated
    – nbk
    Jan 6, 2021 at 0:07
  • 1
    show us at least some sample input and output- your post is hard to understand
    – miracle173
    Jan 6, 2021 at 0:18
  • @nbk there is no need to change to "proper join", especially if one wants to use this comma syntax.
    – miracle173
    Jan 6, 2021 at 0:27
  • 1
    @miracle173 No need to, but using old-style joins is a bad habit because it is very error-prone. MS has deprecated the outer join version - which is another reason not to use it.
    – SMor
    Jan 6, 2021 at 0:30

1 Answer 1

1

The current version gives you an error because you're trying to get one aggregate function (average) over another one (count) ; it's not related to round. As far as I understand, you want to output id_theater, id_spectacle, count of tickets per theater and spectacle, and also average number of tickets per theater. One way (most likely not the fastest from performance perspective) would be

with t1 as 
(SELECT
    t.id_theater
    , s.id_spectacle
   , COUNT(tick.id_ticket) as num_tickets
   
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
)
select  id_theater,id_spectacle , num_tickets, ROUND(avg(num_tickets) over (partition by id_theater),2) as avg_per_theater
from t1

Also, I'd suggest using ANSI-92 join instead of ANSI-89 syntax, it's way more readable

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.