CODEWITHSUNDEEP
sql-servert-sql
Max Payne
Max Payne

Reputation: 389

Calculate Weighted Average in SQL Server

I am trying to calculate a 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

I then need to calculate the average based on the time difference rounded down meaning:

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

So in this case it would be:

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

I have already written the SQL query to calculate the aggregate 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 do a subquery? I am not proficient and I have tried but keep getting syntax errors.

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

Upvotes: 4

Views: 2625

Answers (1)

MayowaO
MayowaO

Reputation: 370

If what you mean by "weighted average" is average of all time differences, then the following may be helpful:

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

Upvotes: 4

Related Questions