CODEWITHSUNDEEP
mysqlruby-on-railsactiverecord
yekta
yekta

Reputation: 3433

GROUP BY with Distinct and Count for Active Record (Rails)

Using: Rails 2.3.8 ruby 1.8.7

How can I convert the following query into Rails (Active-Record)

MYSQL

SELECT distinct source_code, count(source_code)
FROM table
GROUP BY 1

Returns =>

| source_code | count |
| ABC         | 16    |
| XYZ         | 2     |

Rails Attempt

Model.find(:all, :select => "distinct source_code, count(source_code)", 
                 :group => 1)

=> [Model source_code: "ABC">, Model source_code: "XYZ">]

As you can see, count is missing from the result set.

Upvotes: 0

Views: 3879

Answers (1)

Harish Shetty
Harish Shetty

Reputation: 64363

Your SQL statement doesn't require the DISTINCT clause as the GROUP BY clause will return distinct sourcecode rows.

In rails 2.3.x you can get the desired result as follows:

Model.count(:group => :source_code)

# returns an ordered hash
{
  "ABC" => 16,
  "XYZ" => 2
}

Upvotes: 2

Related Questions