CODEWITHSUNDEEP
mysqlruby-on-railsruby
23tux
23tux

Reputation: 14736

Group by multiple columns and count results

How do I group by multiple columns, and return the overall results as count with ActiveRecord (Rails 3) and MySql? I tried it with

MyModel.count(group: [:col1, :col2])

but this returns a big hash (there are about 75.000 records in the table), and I only need the overall row count that this query returns.

I could do

MyModel.count(group: [:col1, :col2]).count # => 5266

but this would load all the stuff into Rails, which gets slow.

Upvotes: 0

Views: 435

Answers (2)

Serdar Dogruyol
Serdar Dogruyol

Reputation: 5157

You can use distinct count of each field like this.

MyModel.pluck('COUNT(DISTINCT col1) as col1, COUNT(DISTINCT col2) as col2')

And you receive a result like this

[[13273, 0]]

Upvotes: 0

Karthik T
Karthik T

Reputation: 31952

Have you tried 

MyModel.group(:col1).group(:col2).count

I noticed this doesnt work with association fields, but works fine with their id columns.

Upvotes: 0

Related Questions