T-SQL average calculation

4
votes

I want to incorporate two average calculations for a bunch of value columns in my select statement.

see this link for my simplified table structure including the desired output calculation: Pastebin

1) moving average:

Month1 = value of the value1-column for that month, Month2 = if sum == 0 then write 0, else avg(Month1 and Month2) and so on.

So for each product, I want the moving average for each month within one year. I have this set up in my Excel but I can't transfer the expression to sql.

2) overall average:

for each product, calculate the average over all years and duplicate the calculated value to all rows for that product.

I hope you can help me out with this. It looks like I need a procedure but maybe it is just a simple statement.

1
sqltsqlsql-server-2012averagemoving-average

1 Answers

5
votes

SQL-Server 2012 supports the analytic functions required to do this:

SELECT  Product,
        Month,
        Year,
        Value,
        AVG_YTD = AVG(Value) OVER(PARTITION BY Year ORDER BY Month),
        AVG_Year = AVG(Value) OVER(PARTITION BY Product, Year),
        AVG_Overall = AVG(Value) OVER(PARTITION BY Product)
FROM    T;

Simplified Example on SQL Fiddle