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! :-) -- John Kopanas john-Iau1QiYlxLpBDgjK7y7TUQ@public.gmane.org Blog: http://www.kopanas.com Conference: http://www.cusec.net Twits: http://www.twitter.com/kopanas --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
On Dec 23, 2007, at 10:58 AM, John Kopanas 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 --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
I was referring to the Enumerable#group_by method... not the group by in mySQL... help someone please? :-) On Dec 23, 2007 5:16 PM, John Kopanas <john-Iau1QiYlxLpBDgjK7y7TUQ@public.gmane.org> wrote:> I was referring to the Enumerable#group_by method... not the group by in > mySQL... help someone please? :-) > > > On Dec 23, 2007 12:16 PM, Phillip Koebbe < phillipkoebbe-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > > > > > On Dec 23, 2007, at 10:58 AM, John Kopanas 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 > > > > > > > > > > > -- > John Kopanas > john-Iau1QiYlxLpBDgjK7y7TUQ@public.gmane.org > > Blog: http://www.kopanas.com > Conference: http://www.cusec.net > Twits: http://www.twitter.com/kopanas >-- John Kopanas john-Iau1QiYlxLpBDgjK7y7TUQ@public.gmane.org Blog: http://www.kopanas.com Conference: http://www.cusec.net Twits: http://www.twitter.com/kopanas --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Controller: @records = Record.find(:all).group_by(&:updated_at) View: <% @records.each do |updated_at,records| %> <h2><%= updated_at %></h2> <% for record in @records %> <%= #record stuff %> <% end %> <% end %> On Dec 24, 2007 10:40 AM, John Kopanas <kopanas-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> I was referring to the Enumerable#group_by method... not the group by in mySQL... help someone please? :-) > > > On Dec 23, 2007 5:16 PM, John Kopanas <john-Iau1QiYlxLpBDgjK7y7TUQ@public.gmane.org> wrote: > > > I was referring to the Enumerable#group_by method... not the group by in > > mySQL... help someone please? :-) > > > > > > On Dec 23, 2007 12:16 PM, Phillip Koebbe < phillipkoebbe-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > > wrote: > > > > > > > > > > > On Dec 23, 2007, at 10:58 AM, John Kopanas 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 > > > > > > john-Iau1QiYlxLpBDgjK7y7TUQ@public.gmane.org > > > > > > Blog: <http://www.kopanas.com>http://www.kopanas.com > > > Conference: <http://www.cusec.net>http://www.cusec.net > > > Twits: <http://www.twitter.com/kopanas>http://www.twitter.com/kopanas > > > > > > > > -- > John Kopanas > <john-Iau1QiYlxLpBDgjK7y7TUQ@public.gmane.org>john-Iau1QiYlxLpBDgjK7y7TUQ@public.gmane.org > > Blog: <http://www.kopanas.com>http://www.kopanas.com > Conference: <http://www.cusec.net>http://www.cusec.net > Twits: <http://www.twitter.com/kopanas>http://www.twitter.com/kopanas > > >-- Ryan Bigg http://www.frozenplague.net Feel free to add me to MSN and/or GTalk as this email. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
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 Bigg <radarlistener-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Controller: @records = Record.find(:all).group_by(&:updated_at) > > View: > > <% @records.each do |updated_at,records| %> > <h2><%= updated_at %></h2> > <% for record in @records %> > <%= #record stuff %> > <% end %> > <% end %> > > > > On Dec 24, 2007 10:40 AM, John Kopanas <kopanas-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > I was referring to the Enumerable#group_by method... not the group by in mySQL... help someone please? :-) > > > > > > On Dec 23, 2007 5:16 PM, John Kopanas <john-Iau1QiYlxLpBDgjK7y7TUQ@public.gmane.org> wrote: > > > > > I was referring to the Enumerable#group_by method... not the group by > > > in mySQL... help someone please? :-) > > > > > > > > > On Dec 23, 2007 12:16 PM, Phillip Koebbe < phillipkoebbe-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > > > wrote: > > > > > > > > > > > > > > > On Dec 23, 2007, at 10:58 AM, John Kopanas 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 > > > > > > > > john-Iau1QiYlxLpBDgjK7y7TUQ@public.gmane.org > > > > > > > > Blog: <http://www.kopanas.com>http://www.kopanas.com > > > > Conference: <http://www.cusec.net>http://www.cusec.net > > > > Twits: <http://www.twitter.com/kopanas> > > > > http://www.twitter.com/kopanas > > > > > > > > > > > > > -- > > John Kopanas > > <john-Iau1QiYlxLpBDgjK7y7TUQ@public.gmane.org> john-Iau1QiYlxLpBDgjK7y7TUQ@public.gmane.org > > > > Blog: <http://www.kopanas.com>http://www.kopanas.com > > Conference: <http://www.cusec.net>http://www.cusec.net > > Twits: <http://www.twitter.com/kopanas> http://www.twitter.com/kopanas > > > > > > > > > -- > Ryan Bigg > http://www.frozenplague.net > Feel free to add me to MSN and/or GTalk as this email. > > > >-- John Kopanas john-Iau1QiYlxLpBDgjK7y7TUQ@public.gmane.org Blog: http://www.kopanas.com Conference: http://www.cusec.net Twits: http://www.twitter.com/kopanas --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
.group_by { |record| record.updated_at.to_date }
On Dec 24, 2007 11:23 AM, John Kopanas
<kopanas-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> 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 Bigg
<radarlistener-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > Controller: @records = Record.find(:all).group_by(&:updated_at)
> >
> > View:
> >
> > <% @records.each do |updated_at,records| %>
> > <h2><%= updated_at %></h2>
> > <% for record in @records %>
> > <%= #record stuff %>
> > <% end %>
> > <% end %>
> >
> >
> >
> > On Dec 24, 2007 10:40 AM, John Kopanas <
kopanas-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> >
> > > I was referring to the Enumerable#group_by method... not the
group by in mySQL... help someone please? :-)
> > >
> > >
> > > On Dec 23, 2007 5:16 PM, John Kopanas
<john-Iau1QiYlxLpBDgjK7y7TUQ@public.gmane.org> wrote:
> > >
> > > > I was referring to the Enumerable#group_by method... not
the group
> > > > by in mySQL... help someone please? :-)
> > > >
> > > >
> > > > On Dec 23, 2007 12:16 PM, Phillip Koebbe <
phillipkoebbe-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
> > > > wrote:
> > > >
> > > > >
> > > > >
> > > > > On Dec 23, 2007, at 10:58 AM, John Kopanas 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
> > > > >
> > > > > john-Iau1QiYlxLpBDgjK7y7TUQ@public.gmane.org
> > > > >
> > > > > Blog:
<http://www.kopanas.com>http://www.kopanas.com
> > > > > Conference:
<http://www.cusec.net>http://www.cusec.net
> > > > > Twits: <http://www.twitter.com/kopanas>
> > > > > http://www.twitter.com/kopanas
> > > > >
> > > >
> > >
> > >
> > > --
> > > John Kopanas
> > > <john-Iau1QiYlxLpBDgjK7y7TUQ@public.gmane.org>
john-Iau1QiYlxLpBDgjK7y7TUQ@public.gmane.org
> > >
> > > Blog: <http://www.kopanas.com>http://www.kopanas.com
> > > Conference: <http://www.cusec.net>http://www.cusec.net
> > > Twits: <http://www.twitter.com/kopanas>
http://www.twitter.com/kopanas
> > >
> > >
> > >
> >
> >
> > --
> > Ryan Bigg
> > http://www.frozenplague.net
> > Feel free to add me to MSN and/or GTalk as this email.
> >
> >
> >
> >
>
>
> --
> John Kopanas
> john-Iau1QiYlxLpBDgjK7y7TUQ@public.gmane.org
>
> Blog: http://www.kopanas.com
> Conference: http://www.cusec.net
> Twits: http://www.twitter.com/kopanas
>
> >
>
--
Ryan Bigg
http://www.frozenplague.net
Feel free to add me to MSN and/or GTalk as this email.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
On Dec 23, 2007, at 6:10 PM, John Kopanas wrote:> I was referring to the Enumerable#group_by method... not the group > by in mySQL... help someone please? :-)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. ;) Peace, Phillip --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---