Need help
Input
Date A B C
2015-10-31 1.49 3.7 7.8
2015-11-30 1.45 3.6 7.6
2015-12-31 1.41 3.7 8.0
2016-01-31 1.33 3.7 8.3
2016-02-29 1.29 4.1 8.6
2016-03-31 1.46 4.4 9.7
CREATE TABLE dbo.ThreeMonth(RDate DATE,A FLOAT,b FLOAT,C FLOAT)
INSERT into dbo.threemonth ( RDate , a , b,c) VALUES ( '2015-10-31' , 1.49, 3.7,7.8) INSERT into dbo.threemonth ( RDate , a , b,c) VALUES ( '2015-11-30' , 1.45, 3.6,7.6)
INSERT into dbo.threemonth ( RDate , a , b,c) VALUES ( '2015-12-31' , 1.41, 3.7,8.0)
INSERT into dbo.threemonth ( RDate , a , b,c) VALUES ( '2016-01-31' , 1.33, 3.7,8.3)
INSERT into dbo.threemonth ( RDate , a , b,c) VALUES ( '2016-02-29' , 1.9, 4.1,8.6)
INSERT into dbo.threemonth ( RDate , a , b,c) VALUES ( '2016-03-31' , 1.46, 4.4,9.7)
INSERT into dbo.threemonth ( RDate , a , b,c) VALUES ( '2016-04-30' , 1.35, 4.3,9.4)
SELECT * FROM threemonth
--Tried the Following query
select rdate, avg(A)
OVER ( ORDER BY Rdate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) FROM threemonth
OutPut
I need TO display rolling 3 months average FOR 3 COLUMNS a,b,c .WHEN I ADD AVG(b) AND AVG(c) it gives ERROR
"Column 'threemonth.RDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
--Also I want the OUTPUT IN following format
2015-10-31 NULL -- Because 3 months are not available avg caot be calculated
2015-11-30 NULL -- Because 3 months are not available avg caot be calculated
2015-12-31 1.45
2016-01-31 1.39666666666667
2016-02-29 1.54666666666667
2016-03-31 1.56333333333333
2016-04-30 1.57 code here
Can somebody advise TO solve the above problem WHERE I need TO TAKE AVG FOR more THEN 1 COLUMN AND display OUTPUT IN the FORMAT I have shown above FOR ALL the 3 COLUMNS?