ORACLE SQL Developer (Query)

Question

The question that I would like to answer is: Present the average number of students who travel in a particular route per week.

What I have tried:

SELECT SUM(NO_OF_SEATS) "WEEKLY AVG"
FROM RESER;

This code shows me the average number of students only, but I want the average per week and I don't know how to get that.

I tried this but it's wrong:

SELECT SUM(NO_OF_SEATS) "WEEKLY AVG"
TO_CHAR(TRUNC(MIN(TRAVEL_DATE), 'WW') + 1, 'FORMAT')
FROM RESER;

• Dresden: What error or issue are you getting with your query you tried?
• xQbert: Some Very odd data if SUM() = AVG()...

Answer 1

Try this:

SELECT AVG(NO_OF_SEATS) "WEEKLY AVERAGE", TRUNC(TRAVEL_DATE,'IW')
FROM RESER
GROUP BY TRUNC(TRAVEL_DATE,'IW');

'WW' = Assumes the first day starts on January 1st and will go in 7 day increments. Weeks will potentially start on a day that is not Monday.
'IW' = Will always start the week on a Monday.

• user5683823: @user5520587 - if MikeS's answer worked for you, you may want to mark it as "Correct Answer" - otherwise the question will continue to show among the unanswered questions. Thank you!
• Dresden: I wouldn't mind having another correct answer under my belt as well. Thanks!