Multiple Sum/Average/Max/etc in one query


#1

Hi all, I was wondering how one does multiple sum/average/max/min in a
single query using AR.

It would be cool to be able to do:

totals = Call.sum([:price,:duration], { :conditions => “destination =
‘UK’”, :group => “location” })

and end up with a hash with totals[:location][:colname]

e.g.:

totals[‘london’][‘price’] = 15
totals[‘london’][‘duration’] = 30
totals[‘leeds’][‘price’] = 20
totals[‘leeds’][‘duration’] = 40
totals[‘manchester’][‘price’] = 10
totals[‘manchester’][‘duration’] = 20

Ideally I would even dream about something that can do:

totals = Call.calculate([:sum,:sum,:count],[:price,:duration,:call], {
:conditions => “destination = ‘UK’”, :group => “source,location” })

Which would give me a hash with totals[:source][:location][:colname]

e.g.:

totals[‘US’][‘london’][‘price’] = 15
totals[‘US’][‘london’][‘duration’] = 30
totals[‘US’][‘london’][‘call’] = 5
totals[‘US’][‘leeds’][‘price’] = 20
totals[‘US’][‘leeds’][‘duration’] = 40
totals[‘US’][‘leeds’][‘call’] = 8
totals[‘FR’][‘london’][‘price’] = 10
totals[‘FR’][‘london’][‘duration’] = 20
totals[‘FR’][‘london’][‘call’] = 5

Any one has been confronted to this problem? How did you sort this out?

My current way of doing it is:

Call.find(:all, :select => “‘price’ = sum(‘price’), ‘duration’ =
sum(duration), ‘call’ = count(call), source, location”, :conditions =>
“destination = ‘UK’”, :group => “source,location” )

Then I iterate through the result collection and make up the hash myself

It works fine but I was hoping to find a Rails way of doing so…

Gael