I am trying to workout the average number of bookings made per day over a given time period.

So far I have this code:

SELECT 
    AVG(NumberOfBookings) 
FROM
    (SELECT 
         DATEPART(weekday, UpdatedDate) AS Days, 
         COUNT(*) AS NumberOfBookings
     FROM 
         Booking
     WHERE 
         Status = 'Confirmed'    
     GROUP BY 
         DATEPART(weekday, UpdatedDate)) AS COUNTS

When I run it I just get 1 result which is the average of all bookings per day rather than an individual average for each day of the week.

I also tried running this:

SELECT Days,
       AVG(NumberOfBookings) 
FROM
    (SELECT 
         DATEPART(weekday,UpdatedDate) AS Days, 
         COUNT(*) AS NumberOfBookings
    FROM 
         Booking
    WHERE 
         Status = 'Confirmed'    
    GROUP BY
         DATEPART(weekday,UpdatedDate)) AS COUNTS
GROUP BY
    Days

But this just gives me the total from each day rather than the average.

The Bookings table includes these columns: BookingID, BookingDate, CustomerID, UpdatedDate, Status

CodePudding user response：

Try this:

Select  t.NameOfDay,t.PerDayBookings, t.TotalBookings
from (
SELECT DATENAME(WEEKDAY,[BookingStartDateTime] ) as NameOfDay,
    count(*) as PerDayBookings,
    SUM(COUNT(*)) OVER() AS TotalBookings
  FROM [Bookings]
  Where [BookingStartDateTime] is not null
  Group by DATENAME(weekday,[BookingStartDateTime] ) 
  
  )as t

CodePudding user response：

I counted the number of confirmed per day and then divided by distinct time to get the average.

select   datepart(weekday, time)                                   as day_of_the_week     
        ,cast(1.0*count(*)/count(distinct time) as decimal (10,2)) as avg_confirmed_per_day
from     t
where    status = 'confirmed'
group by datepart(weekday, time)

Fiddle