SQL query for descending order by sum of values

I have an Apex Oracle app to do.

This is the app description: A touristic destination is defined by its name and description. Each touristic destination has a number of bookings, defined by start date, end date, description, price as in the following example:

Touristic destination: Hotel Sunrise, Hawaii
Start date: 18-Aug-2014
End date: 01-Sep-2014
Description: One room with breakfast included
Price: 2000

For the app I created 2 tables:

Destinations (ID: number, Name: varchar2, Description: varchar2)
Bookings (BID: number, ID: number, start_date: date, end_date: date, description: varchar2, price: number)

I am at the last requirement, which says that I have to make a report that shows destinations ordered descendent by their specific number of bookings. Have some trouble building the query (don’t have an example to show) because the I don’t know how to tie the functions together. I suspect there could be involved SUM, COUNT, ORDER BY, GROUP BY, but can not come up with a valid query for this. I could use some help on building the query.

Answer

You can use the group by as follows:

Select d.id, d.name,
       Count(1) as number_of_bookings
  From destination d left join bookings b
    On b.id = d.id
Group by d.id, d.name
Order by number_of_bookings desc;

Advertisement

Answer