Hi All,
Is there any way to use ActiveRecord calculations while also acquiring relevant field names? For example, I'd like to find the average age of all people with the various last names. Basically I'd like to use:
Person.average(:age, :group_by => "last_name")
... as a substitute for
"Select last_name, AVG(age) FROM People GROUP BY last_name"
Any ideas?
This works pretty much exactly as you have described (though I think you'd use :group instead of :group_by) for single fields. The return is an OrderedHash, so the keys will be the last names and the values will be the average age for that last name.
Since any field that you return has to be included in the group, if you want to add additional fields, you can try doing something a little hackish. In Postgres, for example, you can concatenate the additional fields like so:
Person.average(:age, :group => "first_name || ',' || last_name")
The keys in the return would be the comma separated first and last names. If you're wanting ActiveRecord::Base objects returned, you have to resort to "SELECT ..." style queries.
Person.average(:age, :select => "last_name", :group => "last_name")
It returns an hash where the key is the value for_last name, the value is the result of the average calculation.
To return any attribute on the class you're averaging against you're probably better off using the standard find(:all, ...) method with a custom :select option.
So your example would be:
groups_of_people = Person.find(:all,
:select => 'avg(age) as avg_age, last_name',
:group => 'last_name')
They aren't really Person instances then (although they will be return as such) but you can do:
groups_of_people.each do |g|
g.last_name
g.avg_age
end