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 request?
Thank you!
-
1Do you mean "average in the last 30 days", or "average per day during that calendar month"? How would you like to report averages of incomplete months?Kerrek SB– Kerrek SB2011-07-11 22:18:17 +00:00Commented Jul 11, 2011 at 22:18
Add a comment
|
2 Answers
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.
1 Comment
Konstantin Bodnia
Yeah. That works. But I was sure there was a request without select in select :) But Thank you anyway!
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.
