CODEWITHSUNDEEP
mysqlsql
user460114
user460114

Reputation: 1871

mysql get average data for full months

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?

Upvotes: 7

Views: 16494

Answers (2)

mkjeldsen
mkjeldsen

Reputation: 2180

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,

Upvotes: 12

fancyPants
fancyPants

Reputation: 51938

Can't test it right now, but please have a try with this one:

SELECT 
    DATE_FORMAT(`Date`, '%Y-%m') AS yearMonth,
    SUM(Sales)
FROM
    yourTable
GROUP BY 
    yearMonth
HAVING 
    COUNT(*) = DAY(LAST_DAY(`Date`)

Upvotes: 3

Related Questions