How can I order by grouped and ordered multiple columns?-CodePudding

I want to return the count of bookings of the top 10 (most) booked destinations per weekday (how many customers booked to those top 10 destinations on Monday, Tuesday etc.).

I wrote this query, but I cannot order properly.

select  Destination, DATENAME(WEEKDAY,BookingDate) as Day, count (DATENAME(WEEKDAY,BookingDate)) as Booking
from Booking
GROUP BY Destination, DATENAME(WEEKDAY,BookingDate)
ORDER BY Destination,count(Destination)  desc

It should be like this.

Destination   | Day |        Booking
Portugal     Monday          5
Portugal     Tuesday         4
Portugal     Wednesday       1
Spain        Monday          3
Spain        Tuesday         2
Spain        Monday          3

CodePudding user response：

Your query is a good start. I think that this is what you want.
See the DBfiddle link at the bottom to test further.

SELECT  
  DAYNAME(BookingDate) AS Day, 
  Destination,
  COUNT(BookingDate) AS Bookings
FROM Booking
GROUP BY 
  DayName(BookingDate),
  DayOfWeek(BookingDate),
  Destination
ORDER BY 
  dayofweek(bookingdate),
  Destination

Day       | Destination | Bookings
:-------- | :---------- | -------:
Sunday    | Portugal    |        1
Monday    | Portugal    |        1
Wednesday | Portugal    |        1
Wednesday | Spain       |        4
Saturday  | Portugal    |        4
Saturday  | Spain       |        3

db<>fiddle here