Moving average works, but moving Stdev wont? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Moving average works, but moving Stdev wont?

Author

Topic

Sapereaud
Starting Member

3 Posts

Posted - 2012-08-31 : 04:18:34

Hi,

I'm just putting together a simple moving average and moving standard deviation code, but for some reason the standard deviation wont calculate, all I get is zeros.
I would much appreciate it if someone could point me in the right direction.

The code: Where ID is an unique identifier

SELECT A.ID, AVERAGE(A.[close]), STDEV(A.[close]) as STD
FROM audtest.dbo.AUDJPY53 AS A CROSS JOIN
audtest.dbo.AUDJPY53 AS B
WHERE (A.ID >= 10) AND (A.ID BETWEEN B.ID AND B.ID + 9)
GROUP BY A.ID
ORDER BY A.ID

Cheers,
M

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-31 : 04:45:33

AVERAGE ? Are you using MS SQL Server ?

KH
[spoiler]Time is always against us[/spoiler]

Sapereaud
Starting Member

3 Posts

Posted - 2012-08-31 : 05:57:37

quote:
Originally posted by khtan

AVERAGE ? Are you using MS SQL Server ?

KH
[spoiler]Time is always against us[/spoiler]

Yep the 2012 version

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-31 : 06:31:37

the function name should still be AVG() in 2012.

quote:
but for some reason the standard deviation wont calculate, all I get is zeros

can you post some sample data and the expected result ?

KH
[spoiler]Time is always against us[/spoiler]

Sapereaud
Starting Member

3 Posts

Posted - 2012-08-31 : 06:49:32

Data

Date time ID close
1993.05.16 18:00 1 78.16
1993.05.17 18:00 2 77.2
1993.05.18 18:00 3 76.66
1993.05.19 18:00 4 77
1993.05.20 18:00 5 76.9
1993.05.23 18:00 6 76.9
1993.05.24 18:00 7 76.6
1993.05.25 18:00 8 75.8
1993.05.26 18:00 9 74.4
1993.05.27 18:00 10 73.5
1993.05.30 18:00 11 72.7
1993.05.31 18:00 12 72.5
1993.06.01 18:00 13 72.6
1993.06.02 18:00 14 72.9
1993.06.03 18:00 15 72.8
1993.06.06 18:00 16 72.1
1993.06.07 18:00 17 71.2
1993.06.08 18:00 18 71.9
1993.06.09 18:00 19 71.7
1993.06.10 18:00 20 72.1
1993.06.13 18:00 21 70.9
1993.06.14 18:00 22 71.7
1993.06.15 18:00 23 72.2
1993.06.16 18:00 24 72.2
1993.06.17 18:00 25 74.3
1993.06.20 18:00 26 75.8
1993.06.21 18:00 27 75.8
1993.06.22 18:00 28 73.4
1993.06.23 18:00 29 72.5
1993.06.24 18:00 30 71.3

Result
Date time ID close AVG STD
1993.05.27 18:00 10 73.5 76.312 1.390354072
1993.05.30 18:00 11 72.7 75.766 1.634626699
1993.05.31 18:00 12 72.5 75.296 1.839367041
1993.06.01 18:00 13 72.6 74.89 1.949615347
1993.06.02 18:00 14 72.9 74.48 1.886678445
1993.06.03 18:00 15 72.8 74.07 1.742316466
1993.06.06 18:00 16 72.1 73.59 1.523482852
1993.06.07 18:00 17 71.2 73.05 1.274754878
1993.06.08 18:00 18 71.9 72.66 0.873307888
1993.06.09 18:00 19 71.7 72.39 0.669078969
1993.06.10 18:00 20 72.1 72.25 0.546198987
1993.06.13 18:00 21 70.9 72.07 0.665081449
1993.06.14 18:00 22 71.7 71.99 0.655659126
1993.06.15 18:00 23 72.2 71.95 0.625832779
1993.06.16 18:00 24 72.2 71.88 0.5411921
1993.06.17 18:00 25 74.3 72.03 0.908050415
1993.06.20 18:00 26 75.8 72.4 1.500370325
1993.06.21 18:00 27 75.8 72.86 1.772129917
1993.06.22 18:00 28 73.4 73.01 1.745120181
1993.06.23 18:00 29 72.5 73.09 1.696041142
1993.06.24 18:00 30 71.3 73.01 1.765376888

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources