Cannot perform group operating in rails active record

Question

I am trying to perform group_by operation on my rails model,but there seem to be some problem. I have a model named student as

class CreateStudents < ActiveRecord::Migration
  def change
    create_table :students do |t|
      t.integer :student_id
      t.string :department
      t.integer :maths
      t.integer :physics
      t.integer :chemistry
      t.string :year

      t.timestamps null: false
    end
  end
end

On rails console when I run

 s = Student.all.group("date(created_at)")

I have the following error:

ActiveRecord::StatementInvalid: PG::GroupingError: ERROR:  column "students.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT "students".* FROM "students" GROUP BY date(created_at...
               ^
: SELECT "students".* FROM "students" GROUP BY date(created_at)

However when I run

 s = Student.all.group("date(created_at)").count

it runs successfully.

I am a new to rails.

Rails 4.2.6 Ruby 2.3.1

Possible Duplicate [Link](http://stackoverflow.com/questions/18061285/postgresql-must-appear-in-the-group-by-clause-or-be-used-in-an-aggregate-functi) — Pravesh Khatri, Jun 13 '16 at 05:17

Aleksei Matiushkin · Answer 1 · 2016-06-13T05:23:50.410

0

What do you expect to be a result of group without count?

Imagine you have a table:

   id | created_at
——————|———————————
    1 | 2016-06-12
    2 | 2016-06-12
    3 | 2016-06-12
    4 | 2016-06-13
    5 | 2016-06-13

Now with count the result would be:

count | created_at
——————|———————————
    3 | 2016-06-12
    2 | 2016-06-13

But without count there is a collision (first column value is undetermined.)

The problem has nothing to do with Rails, it is a typical SQL problem with non well-formed aggregation. Please share, what you actually wanted to achieve (to receive as a result of this operation.)

Whether you want to group the result on Ruby side, get the array and group it with Ruby Enumerable#group_by, on the array instance (please note, this is rather inefficient):

Model.all.group_by &:created_at

edited Jun 13 '16 at 05:23

answered Jun 13 '16 at 05:16

Aleksei Matiushkin

119,336
10
100
160

I want all the fields grouped by `created_at`. Please tell me how to achieve that if I am wrong. I want average marks of all student with same student_id – aks Jun 13 '16 at 05:18
What do you mean “all the fields”? Given the very simple input data I provided, what would be the desired result? – Aleksei Matiushkin Jun 13 '16 at 05:19
I want average marks of every student with same student_id, can I do that? – aks Jun 13 '16 at 05:21
Why would you group by `created_at` if you need _averages_ for _id_?? `Student.group_by(:student_id).average(:maths)` would do. http://api.rubyonrails.org/classes/ActiveRecord/Calculations.html – Aleksei Matiushkin Jun 13 '16 at 05:25
can I find average for multiple fields like? `Student.group(:student_id).average(:maths, :physics)`? – aks Jun 13 '16 at 05:32
`group_by` should be `group` in the above comment! – aks Jun 13 '16 at 05:34
The only possibility I am aware of is to use plain SQL: `Student.connection.execute "SELECT AVG(...), AVG(...) FROM students GROUP BY student_id"`. – Aleksei Matiushkin Jun 13 '16 at 05:42

Uday kumar das · Answer 2 · 2016-06-13T05:20:00.217

0

use this code:

 s = Student.group(:created_at)

If you want to use group instead of group_by then don't use all because it returns array of objects.

using group_by:

s = Student.all.group_by(&:created_at)

edited Jun 13 '16 at 05:20

answered Jun 13 '16 at 05:17

Uday kumar das

1,615
16
33

The former produces the very same error due to reasons I described in my answer, the latter is _grouping_ the array on the ruby/client side. – Aleksei Matiushkin Jun 13 '16 at 05:19

Cannot perform group operating in rails active record

2 Answers2