SQL Query .. a little help with AVG and MEDIAN using DISTINCT and SUM

Question

I have a query to get the total duration of phone usage for various users...

But I need to be able to work out distinct averages for their usage.. the problem being certain users share phones and I can only grab phone info, so the call duration is repeated and this would skew the data..

So I need an average and a distinct (on the pin.Number field)... it would also be useful to do a Median if that is possible..??

This is the current query...

SELECT TOP 40 SUM(Duration) AS TotalDuration, c.Caller, oin.Name, oin.Email, pin.Number, oin.PRN 
FROM Calls as c 
INNER JOIN Phones as pin On c.caller = pin.id 
INNER JOIN officers as oin On pin.id = oin.fk_phones 
WHERE Duration <> 0 AND Placed BETWEEN '01/07/2011 00:00:00' AND '20/08/2011 23:59:59' 
GROUP BY c.Caller, oin.Name, pin.Number, oin.Email, oin.PRN 
ORDER BY TotalDuration DESC  

Many thanks for any pointers

Here's an example of the current data I am after (but I have added the averages below which is what I am after), as you can see some users share the same phone but the number of seconds is shared between them so don't want that to influence the average (I don't want 11113 seconds repeated), so there needs to be a distinct on each phone number..

Answer 1

I hope you can use this, I did it with temporary tables

declare @calls table (number char(4), duration int)
declare @officers table(number char(4), name varchar(10))

insert @calls values (3321,1)
insert @calls values (3321,1)
insert @calls values (3321,1)
insert @calls values (3321,42309)

insert @calls values (1235,34555)
insert @calls values (2979,31133)
insert @calls values (2324,24442)
insert @calls values (2345,11113)
insert @calls values (3422,9922)
insert @calls values (3214,8333)


insert @officers values(3321, 'Peter')
insert @officers values(1235, 'Stewie')
insert @officers values(2979, 'Lois')
insert @officers values(2324, 'Brian')
insert @officers values(2345, 'Chris')
insert @officers values(2345, 'Peter')
insert @officers values(3422, 'Frank')
insert @officers values(3214, 'John')
insert @officers values(3214, 'Mark')

Sql to get median and average

;with a as 
(
select sum(duration) total_duration, number from @calls group by number
)
select avg(a.total_duration) avg_duration, c.total_duration median_duration from a
cross join (
select top 1 total_duration from (
select top 50 percent total_duration from a order by total_duration desc) b order by
total_duration) c
group by c.total_duration

Try here: https://data.stackexchange.com/stackoverflow/q/108612/

Sql To get the Total durations

select o.name, c.total_duration, c.number from @officers o join
(select sum(duration) total_duration, number from @calls group by number) c
on o.number = c.number
order by total_duration desc

Try here: https://data.stackexchange.com/stackoverflow/q/108611/