Rails - Sep 2007 - How do you total up the number of Posts per Month?

Let''s say I want to count the total number of posts per month using the
created_at column. For instance, I want to display a list-like format
like so:

August (20 posts)
July (12 posts)
June (16 posts)

[In my controller, I have @posts = Post.find(:all)]

How would you be able to total up the # of posts per month?
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Use find_by_sql, having sql query:

select created_at, count(created_at) as num_of_posts from posts
group by created_at
order by created_at desc
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Ivan Trajkovic wrote:
> Use find_by_sql, having sql query:
> 
> select created_at, count(created_at) as num_of_posts from posts
> group by created_at
> order by created_at desc
I think instead you want;

SELECT
  MONTH(created_at) AS month,
  YEAR(created_at)  AS year,
  COUNT(*)          AS number_of_posts
FROM posts
GROUP BY year, month

you could also do this inside a normal fine if you wanted

find(:all,
  :select => "MONTH(created_at) AS month, YEAR(created_at) AS year, 
COUNT(*) AS number of posts",
  :group => "year, month"
  )

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---