Group, count, having, and order by in Rails

ruby-on-rails rails-activerecord
Don P picture Don P · Jan 7, 2015 · Viewed 17.1k times · Source

I have a table: people with a column named: age.

How can I get a count of the people with each age, ordered from oldest to youngest, filtered by ages with at least 2 people in it?

How I would write it in raw SQL:

SELECT
  COUNT(1) AS people_count,
  age
FROM people
GROUP BY age
HAVING people_count > 1
ORDER BY age DESC

In Rails (I'm not sure how to do it):

Person.group(:age).count will get me the counts by age, but I can't figure out how to order it descendingly by age, or add the having clause.

Answer

eugen picture eugen · Jan 7, 2015

Try something like:

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

Related questions

How do you delete an ActiveRecord object?

How do you delete an ActiveRecord object? I looked at Active Record Querying and it does not have anything on deleting that I can see. Delete by id, Delete the current object like: user.remove, Can you delete based on …

Read More
Rails 4: List of available datatypes

Where can I find a list of data types that can be used in Ruby on Rails 4? Such as text string integer float date I keep learning about new ones and I'd love to have a list I could easily …

Read More
Add a reference column migration in Rails 4

A user has many uploads. I want to add a column to the uploads table that references the user. What should the migration look like? Here is what I have. I'm not sure if I should use (1) :user_id, :int …

Read More