MySQL.AVG

Transact-SQL
1

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

2

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

This is in t SQL

This is SQL server forum

1 Like
4

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?

5

Hi

I will create sample data
And post the solution tomorrow

My bed time now

Thanks

1 Like
6

Thanks so much! have a good sleep!

7

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

image
image1283×1330 30.2 KB

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

image
image952×328 6.28 KB

1 Like
8

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:

9

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
10

Thanks for your help!!