CODEWITHSUNDEEP
ruby-on-railsrails-activerecord
Don P
Don P

Reputation: 63778

Group, count, having, and order by in Rails

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.

Upvotes: 37

Views: 41111

Answers (3)

eugen
eugen

Reputation: 9226

Try something like:

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

Upvotes: 53

minh
minh

Reputation: 181

I find the other answers didn't work for me. I had to do something like this

Person.group(:age).having('count(*) > 1').order('age desc').count

Upvotes: 14

Kuba Płoskonka
Kuba Płoskonka

Reputation: 177

Person.select('COUNT(1) as people_count').order('age DESC').group(:age).having('people_count > 1')

Upvotes: 3

Related Questions