Rolling Average SQL

How many scripts you need to see for rolling average (AVG) in SQL? Well, it never hurts to see another one...
This is the beauty of Windows Functions, they take a complicated problem and put it in a simple script.

(BTW - I've taken the Sales numbers from here - a great webiste, but I believe it's for PostgresSQL, and then added the year 2016 and the extra numbers. I believe you'll be able to find the pattern.)

CREATE TABLE #temp  (StartDate DATE, Sales INT)

INSERT #temp (StartDate, Sales)
VALUES ('20150110', 5)
,('20150210', 3)
,('20150310', 7)
,('20150410', 8)
,('20150510', 2)
,('20150610', 3)
,('20150710', 6)
,('20160110', 8)
,('20160210', 6)
,('20160310', 10)
,('20160410', 11)
,('20160510', 5)
,('20160610', 6)
,('20160710', 9)



If you just want the rolling average (you can replace the AVG function with SUM, MIN, MAX as well) for all the time periods, you need to run the following code:


SELECT
StartDate,
AVG (sales) OVER ( order by StartDate ) AS AVGSales
FROM #temp


But if you want to partition by YEAR, for example, you'd better run the following code:


SELECT
StartDate,
AVG (sales) OVER (Partition  by YEAR (StartDAte) order by StartDate ) AS AVGSales
FROM #temp



Short & sweet!

(this is a simple example; I need to run this on a total assets for a fund. It's amazing how those extra digits get us confused!)

Don't forget :)

DROP TABLE #temp