CODEWITHSUNDEEP
mysqlaggregateaggregate-functions
Konstantin Bodnia
Konstantin Bodnia

Reputation: 1513

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 request?

Thank you!

Upvotes: 1

Views: 1039

Answers (2)

Bohemian
Bohemian

Reputation: 424963

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.

Upvotes: 3

Kerrek SB
Kerrek SB

Reputation: 476930

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.

Upvotes: 0

Related Questions