MySQL Aggregate function in other aggregate function

I'm having a table with posts. Like (id int, date datetime). How can I select average posts per day count for each month with one sql r开发者_高级运维equest?

Thank you!

This should do it for you:

select month, avg(posts_per_day)
from (select day(date), month(date) as month, count(*) as posts_per_day
      from posts group by 1,2) x
group by 1

Explanation: Because you are doing an aggregate on an aggregate, there is no getting around doing a query on a query:

• The inner query calculates the number per day and captures the month.
• The outer query averages this count , grouping by month.

You can get the number of posts per month like this:

SELECT COUNT(*) AS num_posts_per_month FROM table GROUP BY MONTH(date);

Now we need the number of days in a month:

SELECT COUNT(*) / DATEDIFF(MAKEDATE(YEAR(date), MONTH(date)) + INTERVAL 1 MONTH, MAKEDATE(YEAR(date), MONTH(date))) AS avg_over_month
FROM table GROUP BY MONTH(date);

This will get the average number of posts per day during the calendar month of the post. That is, averages during the current month will continue to rise until the end of the month. If you want real averages during the current month, you have to put in a conditional to get the true number of elapsed days.