MySQL query to get array of averages of each month's data

Question

I have a table named historical_currencies_rate where I have saved last 8 years currency rate of some known currencies. eg (EUR-USD). Table looks like this

+---------------------------+---------------+------------+
|       rate_date           | currency_pair |    rate    |
+---------------------------+---------------+------------+
|       2006-01-01          |    EUR-USD    |  1.1797    |
|       2006-02-01          |    EUR-USD    |  1.1826    |
|       2006-03-01          |    EUR-USD    |  1.1875    |
|       2006-04-01          |    EUR-USD    |  1.2083    |
|       2006-05-01          |    EUR-USD    |  1.2088    |
|       2006-06-01          |    EUR-USD    |  1.2093    |
|       2006-07-01          |    EUR-USD    |  1.2093    |
|       2006-08-01          |    EUR-USD    |  1.2093    |
|       2006-01-01          |    JPY-USD    |  0.01275   |
|       2006-02-01          |    JPY-USD    |  0.01275   |
|       2006-03-01          |    JPY-USD    |  0.01275   |
|       2006-04-01          |    JPY-USD    |  0.01275   |
|       2006-05-01          |    JPY-USD    |  0.01275   |
|       2006-06-01          |    JPY-USD    |  0.01275   |
|       2006-07-01          |    JPY-USD    |  0.01275   |
|       2006-08-01          |    JPY-USD    |  0.01275   |
|       2006-01-01          |    GBP-USD    |  1.559     |
|       2006-02-01          |    GBP-USD    |  1.559     |
|       2006-03-01          |    GBP-USD    |  1.559     |
|       2006-04-01          |    GBP-USD    |  1.559     |
|       2006-05-01          |    GBP-USD    |  1.559     |
|       2006-06-01          |    GBP-USD    |  1.559     |
|       2006-07-01          |    GBP-USD    |  1.559     |
|       2006-08-01          |    GBP-USD    |  1.559     |
|       2006-01-01          |    AUD-USD    |  1.0515    |
|       2006-02-01          |    AUD-USD    |  1.0515    |
|       2006-03-01          |    AUD-USD    |  1.0515    |
|       2006-04-01          |    AUD-USD    |  1.0515    |
|       2006-05-01          |    AUD-USD    |  1.0515    |
|       2006-06-01          |    AUD-USD    |  1.0515    |
|       2006-07-01          |    AUD-USD    |  1.0515    |
|       2006-08-01          |    AUD-USD    |  1.0515    |
|       2006-01-02          |    EUR-USD    |  1.2092    |
|       2006-02-02          |    EUR-USD    |  1.2066    |
|       2006-03-02          |    EUR-USD    |  1.2061    |
|       2006-04-02          |    EUR-USD    |  1.2061    |
|       2006-05-02          |    EUR-USD    |  1.2061    |
|       2006-06-02          |    EUR-USD    |  1.1981    |
|       2006-07-02          |    EUR-USD    |  1.1973    |
|       2006-08-02          |    EUR-USD    |  1.1948    |
+---------------------------+---- ----------+------------+

here I have shown you tha data of few currency upto 8 days and for year 2006 but i have a huge data of 18 currencies with 30 days data upto 2013.

My query is that I want an array which contains the avg currency rate of a particular currency on monthly basis. for eg avg of EUR-USD in my example for the month of January is 1.19935, JPY-USD is 0.01275, GBP-USD is 1.559 and avg of EUR-USD for the month of February is 1.2030375 etc.

I want array of averages of EUR-USD or may be some other currency pair, of each month ie. it should be like

`[1.19935, 1.2030375, ......]`

query-result = [avg. of EUR-USD for January, 2006 , avg. of EUR-USD for February, 2006, ............., avg. of EUR-USD for January, 2013, ..... ] and so on up to 8 years.

Please help me in writing this in single query.Thanks

Answer 1

Take parts of date you want (in your case year and month) and group result on this base and currency:

SELECT DATE_FORMAT(rate_date,'%M, %Y') AS rate_month,currency_pair,AVG(rate) AS avg_rate 
FROM historical_currencies_rate
WHERE currency_pair='EUR-USD'
GROUP BY DATE_FORMAT(rate_date,'%M, %Y'),currency_pair
ORDER BY rate_month