Rolling average

    Date: 10/28/08 (MySQL Communtiy)    Keywords: mysql, sql

    I have a table as below (Slightly truncated, but the idea is there)

    mysql> SELECT date, (sat0700 + sat0800 + sat0900 + sat1000) FROM alerts WHERE date LIKE '2008%' ORDER BY date ASC;

    +------------+-----------------------------------------+
    | date       | (sat0700 + sat0800 + sat0900 + sat1000) |
    +------------+-----------------------------------------+
    | 2008-09-13 |                                      39 |
    | 2008-09-20 |                                       0 |
    | 2008-09-27 |                                       9 |
    | 2008-10-04 |                                      16 |
    | 2008-10-11 |                                       9 |
    | 2008-10-18 |                                       4 |
    | 2008-10-25 |                                       1 |
    +------------+-----------------------------------------+


    I'd like to do some kind of select that adds a new column to the output for a rolling average

    So...

    mysql> SELECT date, (sat0700 + sat0800 + sat0900 + sat1000) AND SOMETHING ELSE FROM alerts WHERE date LIKE '2008%' ORDER BY date ASC;
    +------------+-----------------------------------------+---------+
    | date       | (sat0700 + sat0800 + sat0900 + sat1000) | average |
    +------------+-----------------------------------------+---------+
    | 2008-09-13 |                                      39 |       39|
    | 2008-09-20 |                                       0 |     19.5|
    | 2008-09-27 |                                       9 |       16|
    | 2008-10-04 |                                      16 |       16|
    | 2008-10-11 |                                       9 |     14.6|
    | 2008-10-18 |                                       4 |    12.83|
    | 2008-10-25 |                                       1 |    11.14|
    +------------+-----------------------------------------+---------+


    How would I go about writing such a query? The closest I've got so far is a running total, but can't seem to make the example fit an average

    Source: http://community.livejournal.com/mysql/132135.html

« Selective Replication in... || Memory leak? »


antivirus | apache | asp | blogging | browser | bugtracking | cms | crm | css | database | ebay | ecommerce | google | hosting | html | java | jsp | linux | microsoft | mysql | offshore | offshoring | oscommerce | php | postgresql | programming | rss | security | seo | shopping | software | spam | spyware | sql | technology | templates | tracker | virus | web | xml | yahoo | home