SQL Average Daily Bookings

1

I'm calculating the typical daily booking volume over a certain time frame.

Currently have this code.

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

Instead of getting a separate average for each day of the week when I run it, I just receive 1 result that represents the average of all bookings each day.

2

You need to include day of the week in your outer select.

1 Like
4
create sample data

drop table #Booking

create table #Booking (UpdatedDate date , Status varchar(20))

insert into #Booking select '2022-09-07','Confirmed'
insert into #Booking select '2022-09-07','Confirmed'
insert into #Booking select '2022-09-07','Confirmed'

insert into #Booking select '2022-09-08','Not Confirmed'
insert into #Booking select '2022-09-08','Confirmed'
insert into #Booking select '2022-09-08','Not Confirmed'

insert into #Booking select '2022-09-09','Confirmed'
insert into #Booking select '2022-09-09','Confirmed'
insert into #Booking select '2022-09-09','Not Confirmed'
insert into #Booking select '2022-09-09','Not Confirmed'
insert into #Booking select '2022-09-09','Confirmed'
insert into #Booking select '2022-09-09','Confirmed'

select 'Data',* from #Booking

SELECT 
   DATEPART(weekday, UpdatedDate) AS WeekDay, 
   sum(case when Status = 'Confirmed' then 1 else 0 end)*1.0 /
   count(status)*1.0 as AVG
FROM 
   #Booking
GROUP BY 
         DATEPART(weekday, UpdatedDate)

image

1 Like
5 
SELECT 
   DATENAME(weekday, UpdatedDate) AS WeekDay, 
   sum(case when Status = 'Confirmed' then 1 else 0 end)*1.0 /
   count(status)*1.0 as AVG
FROM 
   #Booking
GROUP BY 
         DATENAME(weekday, UpdatedDate)

image

1 Like
6

thanks, @harishgg1 this is really helpful.

7

Thanks for the suggestion @ahmeds08