SQLTeam.com | Weblogs | Forums

MySQL.AVG

Table ' travel
Colums: trip, departure_date, return_date, van_no, driver_no
calculate the average number of trips per driver in each month

Select AVG(sum(trip)) over ( partition by month , driver )
From table

This is in t SQL

This is SQL server forum

1 Like

Hello Harishgg1!
The solution is giving me crazy numbers, should I COUNT driver? otherwise I think it's calculating with the whole number, Isn't it?

Hi

I will create sample data
And post the solution tomorrow

My bed time now

Thanks

1 Like

Thanks so much! have a good sleep!

hi piro

what does average mean ???? !!!

i have created sample data .. please check if it looks okay !!!!

please click arrow to the left for Drop Create Sample Data Script
drop table #travel 
go 

create table #travel 
(
trip  int identity(1,1) not null, 
departure_date date, 
return_date date, 
van_no int, 
driver_no int 
) 
go 

insert into #travel select '2019-09-10' , '2019-09-11',22,123
insert into #travel select '2019-09-14' , '2019-09-14',24,123
insert into #travel select '2019-09-15' , '2019-09-16',21,123
insert into #travel select '2019-09-14' , '2019-09-14',24,123

insert into #travel select '2019-10-10' , '2019-10-11',22,123
insert into #travel select '2019-10-14' , '2019-10-14',24,123
insert into #travel select '2019-10-15' , '2019-10-16',21,123
insert into #travel select '2019-10-14' , '2019-10-14',24,123
insert into #travel select '2019-10-17' , '2019-10-17',22,123
insert into #travel select '2019-10-17' , '2019-10-18',24,123
insert into #travel select '2019-10-19' , '2019-10-20',21,123
insert into #travel select '2019-10-21' , '2019-10-21',24,123
go 

insert into #travel select '2019-09-10' , '2019-09-11',22,452
insert into #travel select '2019-09-14' , '2019-09-14',23,452
insert into #travel select '2019-09-15' , '2019-09-16',22,452
insert into #travel select '2019-09-14' , '2019-09-14',21,452
insert into #travel select '2019-09-15' , '2019-09-16',22,452
insert into #travel select '2019-09-17' , '2019-09-18',23,452
insert into #travel select '2019-09-19' , '2019-09-19',22,452
insert into #travel select '2019-09-20' , '2019-09-20',21,452
insert into #travel select '2019-10-21' , '2019-10-22',21,452
insert into #travel select '2019-10-23' , '2019-10-24',21,452
go 

select * from #travel
go

i did not understand what you mean by average
I just took count !!!

please click arrow to the left for SQL Script
SELECT 'SQL output',
       datename(MONTH, departure_date) as MonthofTrips,
       Count(trip) as CountTrips, 
       driver_no 
FROM   #travel 
GROUP  BY datename(MONTH, departure_date), 
          driver_no

1 Like

Average, means median, I need to calculate de averarge of trips per driver each month. It must just display two columns, one MonthofTrip, Trips (Where need to display the median of trips per driver in each month).
Thanks soo much Harishss1 :slight_smile:

hi

this below article .. shows how to calculate median !!!!
hope this helps :slight_smile: :slight_smile:
we have to apply this to your Data ( sample data )

1 Like

Thanks for your help!!