Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

group by + sum on multiple columns in rails 3

I need to get a list of locations ordered by the number of picture that I have in the DB for these locations, here is my query

Location.select(:city).group(:city).order("SUM(images_count) DESC").sum(:images_count)

This worked like a charm, unfortunately, I now need to add province and country to prevent ambiguities to arise, so I now have this

Location.select(:city, :province, :country).group(:city, :province, :country).order("SUM(images_count) DESC").sum(:images_count)

And this is not working :(

Could someone help me out with this query?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

I'm not very proud of my solution but it works:

Location.group(:city, :province, :country)
        .select(:city, :province, :country, "SUM(images_girl_count) as sum_images_count")
        .order("sum_images_count DESC")
        .collect{ |location| [location.city, location.province, location.country, location.sum_images_count] }

Any feedback?


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...