Calculate Weighted Average in SQL Server

Question

I am trying to calculate the weighted average based on the following calculations.

I have a dataset that looks something like this:

item |     Date Sent     |     Date Received
 1   |     2 Feb 10am    |      3 Feb 10am
 1   |     6 Feb 11am    |      6 Feb 12pm
 2   |     2 Feb 10am    |      3 Feb 10am
 2   |     6 Feb 11am    |      6 Feb 12pm

Then I need to calculate the average based on the time difference, rounded down:

Time Diff |   Count |
  1       |    2    |
  12      |    2    |

So in this case it will be:

1 * 2 + 12 * 2 / (12 + 1)

I already wrote an SQL query to compute a pivot table:

select 
    floor(datediff(hh, dateSent, dateReceived)) as hrs,
    count(item) as freq 
from 
    table
group by 
    floor(datediff(hh, dateSent, dateReceived))
having 
    floor(datediff(hh, dateSent, dateReceived)) < 100
order by 
    floor(datediff(hh, dateSent, dateReceived)) asc;

Should I make a subquery? I do not own, and I tried, but keep getting syntax errors.

Can someone help me get the SQL query to get the weighted average?

Answer 1

, "", , :

select AVG(a.hrs) 
from 
(
    select floor(datediff(hh,dateSent,dateReceived)) as hrs,
    count(item) as freq from table
        group by floor(datediff(hh,dateSent,dateReceived))
            having floor(datediff(hh,dateSent,dateReceived)) <100
--              order by floor(datediff(hh,dateSent,dateReceived)) asc
) a