# How to create archive list?

**URL:** <https://www.ruby-forum.com/t/how-to-create-archive-list/104911>\
**Category:** Rails\
**Created:** [May 23, 2007, 8:34pm UTC](https://www.ruby-forum.com/t/how-to-create-archive-list/104911 "2007-05-23T20:34:00Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![jamal](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/35a633/32.png) [@jamal](https://www.ruby-forum.com/u/jamal)\
**Post date:** [May 23, 2007, 8:34pm UTC](https://www.ruby-forum.com/t/how-to-create-archive-list/104911/1 "2007-05-23T20:34:00Z")

</div>

Hello,

I want to do archive list like in any blogs? But I cannot really think  
how it’s done?

Do they group the created\_at and then extract the column, but this would  
work since created\_at have different dates all the long way down?

I hope someone can help 🙂

Thanks  
Regards,  
Jamal

---

<div class="post-metadata">

**Author:** ![jamal](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/35a633/32.png) [@jamal](https://www.ruby-forum.com/u/jamal)\
**Post date:** [May 23, 2007, 9:23pm UTC](https://www.ruby-forum.com/t/how-to-create-archive-list/104911/2 "2007-05-23T21:23:02Z")

</div>

It might vary between databases, but in MySQL you could do something  
like:

:select =\> “DATE\_FORMAT(‘%m/%Y’) as month\_year,COUNT(posts.id) as  
count”  
:group =\> “month\_year”  
:order =\> “month\_year”

That’s just off the top of my head, but the idea is to use a GROUP BY  
clause on month.

On May 23, 12:34 pm, Jamal S. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![jamal](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/35a633/32.png) [@jamal](https://www.ruby-forum.com/u/jamal)\
**Post date:** [May 23, 2007, 10:49pm UTC](https://www.ruby-forum.com/t/how-to-create-archive-list/104911/3 "2007-05-23T22:49:20Z")

</div>

dasil003 wrote:

> It might vary between databases, but in MySQL you could do something  
> like:
> 
> :select =\> “DATE\_FORMAT(‘%m/%Y’) as month\_year,COUNT(posts.id) as  
> count”  
> :group =\> “month\_year”  
> :order =\> “month\_year”
> 
> That’s just off the top of my head, but the idea is to use a GROUP BY  
> clause on month.
> 
> On May 23, 12:34 pm, Jamal S. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

I’m using MySQL database, and I cannot really get your example to  
work…

I have column called “created\_at”

def archive  
@archive = Post.find(:all,  
:select =\> “DATE\_FORMAT(‘%m %Y’) as month\_year,  
COUNT(posts.id) as count”,  
:group =\> “month\_year”,  
:order =\> “month\_year”)  
end

and I’m getting error as follow:

Mysql::Error: You have an error in your SQL syntax; check the manual  
that corresponds to your MySQL server version for the right syntax to  
use near ‘) as month\_year, COUNT(posts.id) as count FROM posts GROUP BY  
month\_year ORDER’ at line 1: SELECT DATE\_FORMAT(‘%m %Y’) as month\_year,  
COUNT(posts.id) as count FROM posts GROUP BY month\_year ORDER BY  
month\_year

---

<div class="post-metadata">

**Author:** ![jamal](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/35a633/32.png) [@jamal](https://www.ruby-forum.com/u/jamal)\
**Post date:** [May 23, 2007, 10:59pm UTC](https://www.ruby-forum.com/t/how-to-create-archive-list/104911/4 "2007-05-23T22:59:02Z")

</div>

I think I got it right now 🙂

I forgot to add the column name to date\_format as below

date\_format(date\_column, ‘%M %D, %Y’)