Weighted average in T-SQL (like Excel's SUMPRODUCT)

[Brady Kelly]

I am looking for a way to derive a weighted average from two rows of data with the same number of columns, where the average is as follows (borrowing Excel notation):

(A1*B1)+(A2*B2)+...+(An*Bn)/SUM(A1:An)

The bold part reflects the same functionality as Excel's SUMPRODUCT() function. My catch is that I need to dynamically specify which row gets averaged with weights, and which row the weights come from, and a date range.

[Mycroft Holmes]

Do the whole thing in a stored proc. Create a temp table to hold the interim calculations and then do your processing on the temp table (table var in SQL Server). Your design seems a little odd if you hold both the values to be averaged and the weighting factor in the same table. Row_number() is very useful in this type of processing.

[Niladri_Biswas]

If I have understand your requirement, then try this SET DATEFORMAT dmy declare @tbl table(A int, B int,recorddate datetime,KPI varchar(50)) insert into @tbl select 1,10 ,'21/01/2009', 'Weighty'union all select 2,20,'10/01/2009', 'Tons Milled' union all select 3,30 ,'03/02/2009', 'xyz'union all select 4,40 ,'10/01/2009', 'Weighty'union all select 5,50 ,'05/01/2009', 'Tons Milled'union all select 6,60,'04/01/2009', 'abc' union all select 7,70 ,'05/01/2009', 'Weighty'union all select 8,80,'09/01/2009', 'xyz' union all select 9,90 ,'05/01/2009', 'kws' union all select 10,100,'05/01/2009', 'Tons Milled'

select SUM(t1.A*t2.A)/SUM(t2.A)Result from
(select RecordDate,A,B,KPI from @tbl)t1
inner join(select RecordDate,A,B,KPI from @tbl t)t2
on t1.RecordDate = t2.RecordDate
and t1.KPI = t2.KPI

Hope this helps :)

Niladri Biswas

modified on Monday, November 9, 2009 12:00 AM