mysql get average data for full months

Question

Given the following sample data: tblData ``` Date Sales ---------------------- 2011-12-01 122 2011-12-02 433 2011-12-03 213 ... 2011-12-31 235 2011-11-01 122 2011-11-02 433 2011-11-03 213 ... 2011-11-30 235 2011-10-10 122 2011-10-11 433 2011-10-12 213 ... 2011-10-31 235 ``` Notice that October data begins at 10 October, whereas subsequent months have complete data. I need to get the average monthly sales over all complete months, which in this case would be November and December 2011. How would I do this?

Accepted Answer

```
SELECT `date`, AVG(`sales`)
FROM sales
GROUP BY YEAR(`date`), MONTH(`date`)
HAVING COUNT(`date`) = DAY(LAST_DAY(`date`));
```

Example

If you want to limit the result, either

```
HAVING ...
ORDER BY `date` DESC LIMIT 3
```

which should always return data for the 3 most recent months, or something like

```
FROM ...
WHERE DATE_FORMAT(CURDATE() - INTERVAL 3 MONTH, '%Y-%m')
   <= DATE_FORMAT(`date`, '%Y-%m')
GROUP BY ...
```

which should return data for the 3 previous months, if there is any. I'm not sure which is better but I don't believe `WHERE` gets to use any index on `date`, and if you're using `DATETIME` and don't format it you'll also be comparing the days and you don't want that,

mysql get average data for full months

Solution

Problem