Group by and Count Using Activerecord

GROUP BY and COUNT using ActiveRecord

Distinct and Group By are going to give you different results. To get the results you expect you'll want to use

Person.group(:name).count
(1.2ms)  SELECT COUNT(*) AS count_all, name AS name FROM "people" GROUP BY "people"."name"
=> {"Dan"=>3, "Dave"=>2, "Vic"=>1} 

Seen above, group will return things as a hash. While distinct just returns the number of people in total, seen below.

Person.distinct(:name).count
(0.4ms)  SELECT DISTINCT COUNT(DISTINCT "people"."id") FROM "people"
=> 6 

Group and count in Rails

You can do count on line_items which will return you an ordered hash of device_id and count.

@project.line_items.group(:device_id).count

Summary Count by Group with ActiveRecord

I gave up on trying to do this the ActiveRecord way. Instead I just constructed my query into a string and passed the string into

ActiveRecord::Base.connection.execute(sql_string)

This had the side effect that my result set came out as a array instead of a set of objects. So getting at the values went from a syntax (where user_data is the name assigned to a single record from the result set) like

user_data.total_count

to

user_data['total_count']

But that's a minor issue. Not worth the hassle.

ActiveRecord Count to count rows returned by group by in Rails

The workaround for my situation seems to be to replace the :group => 'tags.id' with :select => 'DISTINCT tags.id' in the options hash before executing the count.

count_options = options.clone
count_options.delete(:order)

if options[:group]
  group_by = count_options[:group]
  count_options.delete(:group)
  count_options[:select] = "DISTINCT #{group_by}"
end

@item_count = @type.count(count_options)

ActiveRecord: count with groups

This is expected behavior.

post.comments.group(:thread).having('COUNT(*) == 1').count

This is returning a hash where the key is the thread id and the value is the count. I believe this is the case anytime you do a group with an aggregate function in rails. You must do the second count to get the number of results that the first query matches.

I am not sure how this would look in Rails, but here is the SQL that I think you want:

SELECT COUNT(*) FROM 
  SELECT COUNT(*) AS count_all, thread AS thread 
  FROM "comments"
  WHERE "comments"."post_id" = 3 
  GROUP BY thread
  HAVING COUNT(*) == 1
  ORDER BY id

Rails association group by count with eager loading

You can use joins instead of includes as shown below.

Article.joins(:author).group("authors.name").count

Activerecord group by count and user_id

Here is the query to meet the need :

Foo.group(:user_id).count

Group, count, having, and order by in Rails

Try something like:

Person.select("id, age").group(:id, :age).having("count(id) > 1").order("age desc")

Want to order with Count, Group by and Order DESC in Rails

Assuming you have a user model, and it contains a has_many relationship with the followings model, you can try with:

User.joins(:followings)
    .order('COUNT(followings.followed_id) DESC')
    .group('users.id')
    .limit(10)

---

Related Topics