Jeremy, I did what you suggested. My query now looks like this -
puts
db[:timesheets].select_group(:start).select_append{count(:start)}.all.to_yaml
I need to extract and group based on the year on the DateTime Column
(:start). Will be grateful for any help.
Thanks,
- Abhijit.
On Tuesday, 11 September 2012 00:42:49 UTC+5:30, Jeremy Evans wrote:
>
> On Monday, September 10, 2012 11:21:58 AM UTC-7, abhijit wrote:
>>
>> Hi Jeremy,
>>
>> I've managed to get rid of the error. I installed the following gems -
>> 'sqlite3' (1.3.6), 'sqlite3-ruby' (1.3.3) and 'sequel' (3.39.0)
>>
>> However, the grouping doesn't seem to work. My code -
>> db = Sequel.sqlite
>> db.create_table? :timesheets do
>> primary_key :id
>> String :project
>> Date :start
>> end
>>
>> table = db[:timesheets]
>> table.insert(:project => 'AAA', :start => DateTime.new(2008, 7, 8, 9,
>> 00))
>> table.insert(:project => 'AAA', :start => DateTime.new(2008, 7, 25, 10,
>> 00))
>> table.insert(:project => 'BBB', :start => DateTime.new(2011, 5, 1, 9,
>> 00))
>> table.insert(:project => 'BBB', :start => DateTime.new(2011, 5, 10, 13,
>> 00))
>> table.insert(:project => 'BBB', :start => DateTime.new(2011, 5, 20, 17,
>> 00))
>>
>> puts table.group(:project).all.to_yaml
>> # ---
>> # - :project: AAA
>> # :start: 2008-07-25 15:30:00 +05:30
>> # :id: 2
>> # - :project: BBB
>> # :start: 2011-05-20 22:30:00 +05:30
>> # :id: 5
>>
>> puts table.group(Sequel.extract(:year, :start)).all.to_yaml
>> # ---
>> # - :project: BBB
>> # :start: 2011-05-20 22:30:00 +05:30
>> # :id: 5
>>
>> It seems only to give me details of the last inserted record.
>> Why? What am I doing wrong?
>>
>>
> Your query is invalid. In SQL, when you use GROUP, you can only SELECT
> expressions that you are GROUPing on and aggregate function calls. SQLite
> allows your invalid query and just returns values for an arbitrary row.
> Stricter databases (e.g. PostgreSQL) would raise an error if given that
> query. For a more detailed explanation, consult a good SQL reference book.
>
> You should probably use select_group instead of group, and then use
> select_append to add any aggregate function calls you need.
>
> Jeremy
>
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sequel-talk/-/_e0p8qhHbxIJ.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
