# Calculate Moving Averages in SQL

I want to calculate the 12 months moving average from a MySQL column. The data represents power measurements in a time series, this is a fairly large dataset (every 10 minutes over several years). A high performance query would be nice, but speed is something I can work out later.

``````DTE                  Active
2012-1-3 00:10       500
2012-1-3 00:20       520
... etc
```

```

The following query gives me the total Asset for each month:

``````SELECT YEAR(DTE) AS year, MONTH(DTE) AS month, SUM(Active)/6 as total FROM saturne s GROUP BY YEAR(DTE), MONTH(DTE)
```

```

The following query gives me the moving average activity for a given month and year - say October 2011.

``````SELECT SUM(Active)/6 AS average FROM saturne
WHERE (YEAR(DTE) = 2011 AND MONTH(DTE) <= 10) OR (YEAR(DTE) = 2010 AND MONTH(DTE) > 10)
```

```

I would like, however, to generate a query that returns the total per month and the 12 months moving average in the next column.

``````year        month        total        average
2012        2            701474       9258089
2012        1            877535       9386664
... etc
```

```

(Factor 6 is that the data represents the instantaneous power recorded every 10 minutes, dividing the total by 6 gives the total energy)

+3

source to share

Try:

``````SELECT YEAR(GDTE) AS year,
MONTH(GDTE) AS month,
SUM(case when i=0 then Active end)/6 as total,
SUM(Active)/(MAX(i+1)*6) as avg
FROM
(select s.*, DATE_ADD(DTE, INTERVAL m.i MONTH) GDTE, m.i
FROM saturne s
cross join (select 0 i union select 1 union select 2 union select 3 union
select 4 union select 5 union select 6 union select 7 union
select 8 union select 9 union select 10 union select 11) m
) sq
WHERE GDTE <= curdate()
GROUP BY YEAR(GDTE), MONTH(GDTE)
```

```
+5

source

All Articles