Ok, I have three tables: theater, spectacles and tickets. A theater has one or more spectacles and for a spectacle were sold one or more tickets* So, id_theater is foreign key in TABLE SPECTACLES and id_spectacle is foreign key in TABLE TICKETS. In table TICKETS, I have ticket_price and id_ticket and with count(id_ticket) I calculate the number of tickets sold for spectacles. And each theater has one or more spectacles. The value for spectacles is calculated as: count(tickets.id_ticket) * tickets.ticket_price. After I calculate the value for spectacles, I need to gather the value of spectacles to find the each value of theater and for this I use SUM().
I need to write a query where I need to calculate the total value obtained by each theater and to be sort by value. For this I need to use the SUM() function. 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 theater.id_theater, SUM(count(tickets.id_ticket) * tickets.id_ticket) total_theater FROM spectacles, theater GROUP BY theater.id_theater ORDER BY total_theater DESC;
For this problem I must use SUM, but I really don’t know what do to at this point and would appreciate any help.