How to use max on sum in oracle sql?

Question

I am working in a task and got stuck at particular question. I am new to SQL so I am reaching out to this platform for the support. Below are the 2 tables. 1st is Theatre_play_table and 2nd is Ticketsales table. Question: List titles, directors and writers of all shows/plays with the highest total sale.

Theatre_play_table

Ticketsales table

I have pasted screenshot of some part of the table. ID column in both the table represents the same information. Last column in Ticketsales table is Totalamount.

I have tried with below query; Select theatre_play.title, theatre_play.director, theatre_play.writer, sum(totalamount) from theatre_play, totalsales where theatre_play.id = totalsales.id group by theatre_play.title, theatre_play.director, Theatre_play.writer order by sum(totalamount) desc fetch first 3 rows only;

The above approach is not useful when data is huge. I wanted to apply max(sum(totalamount)) but oracle threw an error.

Can anyone please help me solve this question?

Thanks. Yes, it works but I wanted to use max function here but failed to apply. — Dex
– Dex, Commented Dec 8, 2021 at 14:35
Move aggregation of sales (by play_id) into the subquery and use fetch first 1 rows with ties, which will return all top-sold shows. Then perform a join. If you will provide sample data as text, I will be able to write a query — astentx
– astentx, Commented Dec 8, 2021 at 16:24

W_O_L_F · Accepted Answer · 2021-12-08 14:41:36Z

1

If I understand you right, the issue is to get the three highest values? Then try something like this:

select * from (
 Select dpro.title, dpro.director, dpro.writer, sum(fth.totalamount) 
 from dpro
 join  fth on dpro.id = fth.id 
  
 group by dpro.title, dpro.director, dpro.writer 
 order by sum(totalamount) desc )
where rownum <=3

answered Dec 8, 2021 at 14:41

W_O_L_F

1,5061 gold badge12 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Dex Over a year ago

Thanks for your response. The task is to find the highest sales - not limited to no. of highest values. I used 3 based on the output. How can I make it for dynamic where all the highest values appear.

SCCJS Over a year ago

@Dhwanil you'd either remove the where clause to get all of them or adjust it for the number of values

Dex Over a year ago

@SCCJS, So if I remove the where clause then all the values will appear. In the above and earlier approach I looked at the data and then decided that there are only 3 highest values. This is not ideal when data is huge. The concern is to get only highest values without looking at the output. I hope this elaborates the questions.

W_O_L_F Over a year ago

@Dhwanil The query sorts the output beginning with the highest value. If you want all the values remove the where clause. If you want the 10 highest replace the 3 with a 10. You are saying that you want the "highest values". That, to me is, not very well defined. It implies that some are not qualifying and should not be viewed.

SCCJS Over a year ago

@Dhwanil when you say highest values, is that values equal to the highest? So if we have 100,100,100,10 it would return 100,100,100? Or are you wanting values greater than the average? Other than that we are just going to give you x number of values

|

Collectives™ on Stack Overflow

How to use max on sum in oracle sql?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related