# Having Problems With group\_by syntax **URL:** \ **Category:** Rails\ **Created:** [December 23, 2007, 5:58pm UTC](https://www.ruby-forum.com/t/having-problems-with-group-by-syntax/124020 "2007-12-23T17:58:37Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![johnk](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/59ef9b/32.png) [@johnk](https://www.ruby-forum.com/u/johnk)\ **Post date:** [December 23, 2007, 5:58pm UTC](https://www.ruby-forum.com/t/having-problems-with-group-by-syntax/124020/1 "2007-12-23T17:58:37Z")
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 K. removed\_email\_address@domain.invalid 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) ---
**Author:** ![johnk](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/59ef9b/32.png) [@johnk](https://www.ruby-forum.com/u/johnk)\ **Post date:** [December 23, 2007, 6:17pm UTC](https://www.ruby-forum.com/t/having-problems-with-group-by-syntax/124020/2 "2007-12-23T18:17:21Z")
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 ---
**Author:** ![johnk](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/59ef9b/32.png) [@johnk](https://www.ruby-forum.com/u/johnk)\ **Post date:** [December 24, 2007, 6:12pm UTC](https://www.ruby-forum.com/t/having-problems-with-group-by-syntax/124020/3 "2007-12-24T18:12:56Z")
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 K. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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](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 K. removed\_email\_address@domain.invalid 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) ---
**Author:** ![johnk](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/59ef9b/32.png) [@johnk](https://www.ruby-forum.com/u/johnk)\ **Post date:** [December 24, 2007, 6:55pm UTC](https://www.ruby-forum.com/t/having-problems-with-group-by-syntax/124020/4 "2007-12-24T18:55:36Z")
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. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > > > in mySQL… help someone please? 🙂 > > > > > > > syntax. > > > > I have a table that records when a task is completed. If I want to > > > > that > > > > Twits: [http://www.twitter.com/kopanas](http://www.twitter.com/kopanas) > > > > Conference: [http://www.cusec.net](http://www.cusec.net)[http://www.cusec.net](http://www.cusec.net) > > > – John K. removed\_email\_address@domain.invalid 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) ---
**Author:** ![johnk](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/59ef9b/32.png) [@johnk](https://www.ruby-forum.com/u/johnk)\ **Post date:** [December 24, 2007, 6:15pm UTC](https://www.ruby-forum.com/t/having-problems-with-group-by-syntax/124020/5 "2007-12-24T18:15:52Z")
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. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > > wrote: > > > > > > seconds, minutes, etc.). So that I can display a list broken up by > > > > from completed\_tasks > > John K. > [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)removed\_email\_address@domain.invalid > > Blog: [http://www.kopanas.com](http://www.kopanas.com)[http://www.kopanas.com](http://www.kopanas.com) > Conference: [http://www.cusec.net](http://www.cusec.net)[http://www.cusec.net](http://www.cusec.net) > Twits: [http://www.twitter.com/kopanas](http://www.twitter.com/kopanas)[http://www.twitter.com/kopanas](http://www.twitter.com/kopanas) > > > – Ryan B. > **[frozenplague.net](http://www.frozenplague.net)** > > This domain may be for sale! Feel free to add me to MSN and/or GTalk as this email. ---
**Author:** ![johnk](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/59ef9b/32.png) [@johnk](https://www.ruby-forum.com/u/johnk)\ **Post date:** [December 24, 2007, 7:10pm UTC](https://www.ruby-forum.com/t/having-problems-with-group-by-syntax/124020/6 "2007-12-24T19:10:17Z")
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? 🙂 > 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 ---
**Author:** ![johnk](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/59ef9b/32.png) [@johnk](https://www.ruby-forum.com/u/johnk)\ **Post date:** [December 24, 2007, 6:56pm UTC](https://www.ruby-forum.com/t/having-problems-with-group-by-syntax/124020/7 "2007-12-24T18:56:13Z")
.group\_by { |record| record.updated\_at.to\_date } On Dec 24, 2007 11:23 AM, John K. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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? 🙂 > > > > > > > > hours, > > > > > something like > > > > > is not an aggregate must be included in the group by. That’s > > > > > Twits: [http://www.twitter.com/kopanas](http://www.twitter.com/kopanas) > > > > > Conference: [http://www.cusec.net](http://www.cusec.net)[http://www.cusec.net](http://www.cusec.net) > > Conference: [http://www.cusec.net](http://www.cusec.net) > Twits: [http://www.twitter.com/kopanas](http://www.twitter.com/kopanas) > > > – Ryan B. > **[frozenplague.net](http://www.frozenplague.net)** > > This domain may be for sale! Feel free to add me to MSN and/or GTalk as this email.