[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    monthly average for last 12 months?
From:       Marco Fioretti <mfioretti () mclink ! it>
Date:       2004-10-18 9:26:01
Message-ID: 1.3.200410181125.11813
[Download RAW message or body]

Greetings,

I have a table with these columns:

Item_Description (varchar) date_purchased (yyyy-mm-dd) amount (float)

I have managed to get the total amount for each month, formatted
in this way:

2001-01        1000
2001-02         540
2001-03        1288

and so on...

I need to add a third column with the monthly _average_ of purchases,
calculated on the current month and the previous eleven. That is:

2001-01  1000 sum(amount from 2000-02-01 to 2001-01-31)/12
2001-02  1000 sum(amount from 2000-03-01 to 2001-02-28)/12
2001-03  1000 sum(amount from 2000-04-01 to 2001-03-31)/12
etc etc...

Can I do it directly in a mysql query, or (doing this from a Perl
script) I need to save the total of each month in the script, and
do the average from there? Examples? Right stuff to read?

Thank you in advance for any help,

Marco


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=mysql@progressive-comp.com

[prev in list] [next in list] [prev in thread] [next in thread]