"Order by" result of "group by" count?

ruby-on-rails ruby-on-rails-3 activerecord sql-order-by

69,654

Solution 1

The easiest way to do this is to just add an order clause to the original query. If you give the count method a specific field, it will generate an output column with the name count_{column}, which can be used in the sql generated by adding an order call:

Message.where('message_type = ?','incoming')
       .group('sender_number')
       .order('count_id asc').count('id')

Solution 2

When I tried this, rails gave me this error

SQLite3::SQLException: no such column: count_id: SELECT  COUNT(*) AS count_all, state AS state FROM "ideas"  GROUP BY state ORDER BY count_id desc LIMIT 3

Notice that it says SELECT ... AS count_all

So I updated the query from @Simon's answer to look like this and it works for me

.order('count_all desc')

69,654

Mohit Jain

warning ! This is just a rip-off my linkedin profile :D Seasoned Web Developer with over 8 years of work experience, with focus on code quality, scaling, timely delivery, under pressure working experience and performance optimization. My responsibilities in the past ranged from designing and implementing large scalable systems, managing and monitoring clusters of servers, and also mentoring junior engineers and managing project teams. I'm always interested in hands-on contributions to challenging and innovative projects. Good interpersonal skills. Uncompromising work ethic and integrity. Known for quickly ramping up on new code bases and incorporating massive design changes in existing systems. Clean and efficient programming style. Excellent debugging practices, used to work in code written by different people. In the last few years, I wrote tens of thousands of lines of code to scale a system from 1 million users to 20 million users single-handedly. Specialities: Ruby on Rails, Redis, Memcache, MySQL, New Relic, Amazon Web Services, Over night prototyping, Mixpanel

Updated on July 05, 2022

Comments

Mohit Jain almost 2 years

This query

Message.where("message_type = ?", "incoming").group("sender_number").count

will return me an hash.

OrderedHash {"1234"=>21, "2345"=>11, "3456"=>63, "4568"=>100}

Now I want to order by count of each group. How can I do that within the query.

Simon Elliston Ball over 12 years

Which database engine are you using? Which version of rails, and what's the generated sql? I've just got a test case (different model) working fine on rails 3.0.7... e.g. @test = Price.where('price is not null').group(:price_date).order('count_price asc').count('price'), which generates SELECT COUNT("prices"."price") AS count_price, price_date AS price_date FROM "prices" WHERE (price is not null) GROUP BY price_date ORDER BY count_price asc
northdig about 10 years

Thank you so much for this. This saved me so much time off my query compared to how I was doing it before! Most important lesson: I learned that the count method 'will generate an output column with the name count_{column}'! Thank you!
sameers about 9 years

Not sure which AR version causes it to use count_all instead of count_id but in 4.2.1, .order("count_id desc") works.
mahemoff over 8 years

Works for me. Maybe you missed the .count('id')?
jeffdill2 over 8 years

I can't believe this only has 35 (now 36) upvotes. So useful!
Carpela almost 8 years

Might be worth specifying whcih DB we're using here. This doesn't work for Postgres. whereas @penner's below does.
Michael Taylor about 7 years

Thanks Simon. Superb!
David Aldridge over 6 years

As of Rails 4+ with PostgreSQL: Message.where(message_type: "incoming").group(:sender_number).order("count(*)").count
Snow Fox over 4 years

Rails 5 with PostgreSQL: Message.where(message_type: "incoming").group(:sender_number).order(:count_all).count
nickcoxdotme about 3 years

Rails 5 with MySQL: Message.where(message_type: "incoming").group(:sender_number).order("count(*)").count