T-SQL average calculation

Question

I want to include two middle calculations for a column of value columns in my select statement.

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

1) moving average:

Month1 = value of the value of the 1-column for this month, month2 = if the sum == 0, then write 0, otherwise avg (Month1 and Month2), etc.

So, for each product, I want a moving average for each month for one year. I have this configured in my Excel, but I cannot pass the expression to sql.

2) overall average:

for each product, calculate the average value for all years and duplicate the calculated value for all rows for this product.

I hope you can help me with this. It sounds like I need a procedure, but maybe it's just an expression.

Answer 1

SQL-Server 2012 supports the analytic functions necessary for 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 SQL Script Example