# Thread: Moving average calculation in 8.4

1. Registered User
Join Date
Dec 2011
Posts
2

## Unanswered: Moving average calculation in 8.4

Hi. Struggling a bit with calculation of a moving average. Basically I have a table with closing prices for stocks and I need to calculate a 10, 50 and 200 day moving average.

So for a 10-day moving average I could get the range by running this query

select "date", "closePrice" from "SecurityData" where "ticker" = 'XYZ' and "date" <= '2011-12-16' order by "date" desc LIMIT 10

It would seem pretty logical (to me anyway) that one could get the value of 10 day moving average just by using the avg function like so:

select avg("closePrice") from "SecurityData" where "ticker" = 'XYZ' and "date" <= '2011-12-16' order by "date" desc LIMIT 10

However I get the error:
'ERROR: column "SecurityData.date" must appear in the GROUP BY clause or be used in an aggregate function
SQL state: 42803
Character: 121'

I've tried to use some window functions, but so far haven't quite figured it out. Would appreciate any help/pointers.

TIA.

Running 8.4

2. Registered User
Join Date
Nov 2003
Posts
2,993

In that case it should as simple as:
Code:
```select date,
avg("closePrice") over (partition by ticker order by date) rows between current row and 20 preceding
from "SecurityData"
where ticker = 'XYZ'
and date <= '2011-12-16'
order by date desc```
The "rows between current row and 20 preceeding" clause was introduced in 9.0 and as far as I understand your question this is exactly what you need.

3. Registered User
Join Date
Dec 2011
Posts
2
Thanks! I can upgrade to 9, although was hoping to avoid it at the moment.
That was actually very close to what I needed (had to make a slight modification though, it's actually 'rows between 9 preceding and current row' instead of 'rows between current row and 10 preceding' to get the 10 day moving average).

Wonder if there's any way to get the query to return just a single number as opposed to a result set?

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•