Having Problems With group_by syntax

Rails
1

I am embarrassed to say I am having problems with the group by syntax.
What I want to do is take a record set that is returned and group it by
the
updated_at field (but only up to the day and not hours, seconds,
minutes,
etc.). So that I can display a list broken up by day.

Can someone help me with the syntax?

I greatly appreciate it! :slight_smile:

–
John K.
[email protected]

Blog: http://www.kopanas.com
Conference: http://www.cusec.net
Twits: http://www.twitter.com/kopanas

2

On Dec 23, 2007, at 10:58 AM, John K. wrote:

I am embarrassed to say I am having problems with the group by syntax.

What I want to do is take a record set that is returned and group
it by the updated_at field (but only up to the day and not hours,
seconds, minutes, etc.). So that I can display a list broken up by
day.

Can someone help me with the syntax?

I have a table that records when a task is completed. If I want to
see how many tasks were completed on each day, I could do something like

select substring(completed, 1, 10) as completed, count(*)
from completed_tasks
group by substring(completed, 1, 10)
order by completed

That’s PostgreSQL, and there is probably a more correct way of doing
it, but that works. Just remember that any column in the select that
is not an aggregate must be included in the group by. That’s where
it starts to get cumbersome.

Peace,
Phillip

3

I was referring to the Enumerable#group_by method… not the group by
in mySQL… help someone please? :slight_smile:

On Dec 23, 2007 5:16 PM, John K. [email protected] wrote:

I am embarrassed to say I am having problems with the group by syntax.
see how many tasks were completed on each day, I could do something like

Blog: http://www.kopanas.com
Conference: http://www.cusec.net
Twits: http://www.twitter.com/kopanas

–
John K.
[email protected]

Blog: http://www.kopanas.com
Conference: http://www.cusec.net
Twits: http://www.twitter.com/kopanas

4

I am almost their… now how can I make updated_at only go down to the
day
level? This solution will divide things up to the second instead of by
day.
I really appreciate everyone’s help!

On Dec 23, 2007 7:14 PM, Ryan B. [email protected] wrote:

in mySQL… help someone please? :slight_smile:

syntax.
I have a table that records when a task is completed. If I want to
that
Twits: http://www.twitter.com/kopanas
Conference: http://www.cusec.nethttp://www.cusec.net

–
John K.
[email protected]

Blog: http://www.kopanas.com
Conference: http://www.cusec.net
Twits: http://www.twitter.com/kopanas

5

Controller: @records = Record.find(:all).group_by(&:updated_at)

View:

<% @records.each do |updated_at,records| %>

<%= updated_at %>

<% for record in @records %> <%= #record stuff %> <% end %> <% end %>

On Dec 24, 2007 10:40 AM, John K. [email protected] wrote:

wrote:

seconds, minutes, etc.). So that I can display a list broken up by
from completed_tasks

John K.
[email protected][email protected]

Blog: http://www.kopanas.comhttp://www.kopanas.com
Conference: http://www.cusec.nethttp://www.cusec.net
Twits: http://www.twitter.com/kopanashttp://www.twitter.com/kopanas

–
Ryan B.

Feel free to add me to MSN and/or GTalk as this email.

6

On Dec 23, 2007, at 6:10 PM, John K. wrote:

I was referring to the Enumerable#group_by method… not the group
by in mySQL… help someone please? :slight_smile:
Hm. Well, sorry about that. I think my problem is I don’t always go
for the Rails purist way to solve things. At least not yet. That’s
twice today that I’ve answered with a SQL solution where I shouldn’t
have. sigh But, hey, if you ever find yourself writing some raw
SQL, just keep in mind what I said. :wink:

Peace,
Phillip

7

.group_by { |record| record.updated_at.to_date }

On Dec 24, 2007 11:23 AM, John K. [email protected] wrote:

<% @records.each do |updated_at,records| %>

I was referring to the Enumerable#group_by method… not the group by in mySQL… help someone please? :slight_smile:

hours,
something like
is not an aggregate must be included in the group by. That’s
Twits: http://www.twitter.com/kopanas
Conference: http://www.cusec.nethttp://www.cusec.net

Conference: http://www.cusec.net
Twits: http://www.twitter.com/kopanas

–
Ryan B.

Feel free to add me to MSN and/or GTalk as this email.