# MySQL - How do I Calculate an Average Value Over a Period of Time When Only Value Changes are Recorded

I am tracking the sales price of various items, and I would like to calculate the average price of each item over a period of time.

My simplified tables look like this:

``````Items
------------------
|key  | title    |
------------------
|1    | Sproket 1|
|2    | Sproket 2|
------------------
``````

And

``````Prices
----------------------------------------
|item_key | price | datetime           |
----------------------------------------
|1        | 2.99  | 2014-11-25 02:05:56|
|1        | 1.99  | 2014-12-13 02:05:56|
|2        | 9.99  | 2014-10-25 02:05:56|
|2        | 8.99  | 2014-11-13 02:05:56|
----------------------------------------
``````

The Items table contains a single row for each item. The Prices table contains a single row for each price change. The script runs daily and checks for the current prices, if the current price is different than the most recent item price recorded in Prices than a new entry is made in Prices.

My question is, How do I calculate the average price of an item over 180 days?

I can't use AVG because there is not a price entry each day for each item.

• That is, you want the `AVG()` of the last price for each day? Commented Apr 6, 2015 at 18:30
• If a price was 40.0 for 60 days and 10.0 for the next 120 days, should the average be calculated as 20.0 or as 25.0 ? Commented Apr 6, 2015 at 20:43
• If the price was \$10.0 for 120 days and \$40.0 60 days the average price should be: \$20.00 Commented Apr 7, 2015 at 20:40
• I have decided it is just too messy to do in SQL with @variables. Either have a price every day (as david suggests) or do the arithmetic in an application language. (I deleted my "Answer" as being wrong and not salvageable.) Commented Apr 7, 2015 at 23:24
• @RickJames, well, glad to know I didn't miss anything. I suspected the answer might be "it can't be done." Rick do you want to toss up a "it can't be done" answer that I can select as the answer? Commented Apr 8, 2015 at 17:13

Well, I started to write SQL to do the task. It got worse and worse. I needed several @variables and perhaps 3 levels of subqueries. So, I decided it was "impossible" in SQL, or at least very messy.

Instead, I would recommend either david's suggestion of having daily prices, which would make `AVG` work easily (probably no @variables or subqueries), or write it in some "real" programming language (PHP, VB, Java, ...).

One ugly was doing date arithmetic -- with bounds of 180 days ago and today. Walking through the data `ASCENDING` led to one problem; `DESC` lead to a different problem.

KISS

Assuming daily means every day including weekends and holidays.

1. Insert price for all items everyday
2. Use AVG() for value
3. Delete prices over 180 days.
• Agree, that would be a simple solution. But the number of rows in the table would increase dramatically, I don't know what the performance hit would be. There are currently 20,000 items, and the prices are generally stable. Commented Apr 7, 2015 at 20:44
• 20K items times 180 rows = 3.6M rows. This is big, but manageable. (1 billion is huge.) Commented Apr 8, 2015 at 18:44

Your probably going to have to use group_concat function to aggregate by key so you can do math on those groups of values.

• When you say do math on the group of values, you mean math outside of mysql? Commented Apr 7, 2015 at 20:48