The following works: >> r = u.utility_data.select("utility_id, sum(ami_residential)").group("utility_id") => #<ActiveRecord::Relation [#<UtilityDatum id: nil, utility_id: 5621>]> >> r.first[:sum] => 263 but when written as a one-liner: >> u.utility_data.select("utility_id, sum(ami_residential)").group("utility_id").first[:sum] it fails with "PG::GroupingError: ERROR: column "utility_data.id" must appear in the GROUP BY clause or be used in an aggregate function" I THINK what''s happening is that the working version generates the following SQL: SELECT utility_id, sum(ami_residential) FROM "utility_data" WHERE "utility_data"."utility_id" = $1 GROUP BY utility_id [["utility_id", 5621]] whereas the failing version adds "ORDER BY" and "LIMIT" to the query: SELECT utility_id, sum(ami_residential) FROM "utility_data" WHERE "utility_data"."utility_id" = $1 GROUP BY utility_id ORDER BY "utility_data"."id" ASC LIMIT 1 [["utility_id", 5621]] which triggers the grumpy ol'' postgresql error. So two questions: - is this expected behavior? - is there a way to inhibit the addition of ORDER BY and LIMIT? Thanks... -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/0626dafcf8c15fad8955eff979f40a01%40ruby-forum.com. For more options, visit https://groups.google.com/groups/opt_out.
BTW, I also tried this: UtilityDatum. select("sum(ami_residential)"). where(:utility => u). group("utility_id"). unscope(:order, :limit). first ... but that still tacks on ORDER BY and LIMIT clauses to the query, so it still fails. Evidently I don''t understand unscope(). -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/e027d962ab05ca67ffbd7c81ce820dea%40ruby-forum.com. For more options, visit https://groups.google.com/groups/opt_out.
Solved (though I''m not entirely sure why this works): UtilityDatum. select("sum(ami_residential)"). where(:utility => u). group("utility_id"). reorder(''''). first The reorder() prevents the ORDER BY clause from being emitted, so the generated SQL is valid. -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/9e71aacaa8c39b5631efe9aa48fc3563%40ruby-forum.com. For more options, visit https://groups.google.com/groups/opt_out.
On Wednesday, 4 December 2013 19:00:31 UTC-5, Ruby-Forum.com User wrote:> > Solved (though I''m not entirely sure why this works): > > UtilityDatum. > select("sum(ami_residential)"). > where(:utility => u). > group("utility_id"). > reorder(''''). > first > > The reorder() prevents the ORDER BY clause from being emitted, so the > generated SQL is valid. > > -- > Posted via http://www.ruby-forum.com/. >The ''ORDER BY'' and ''LIMIT'' clauses are actually coming from the `first` call on the end of the chain. For what it''s worth, if the first query you mentioned is the one you want you may want to check out `ActiveRecord::Calculations`, in particular `sum`: http://api.rubyonrails.org/classes/ActiveRecord/Calculations.html#method-i-sum Your code would look like: r = u.utility_data.group(:utility_id).sum(:ami_residential) At this point, `r` is a Hash: { 42 => 127.25, 76 => 321.02, etc } of utility_id => sum pairs. If you just want data rolled up for *one* Utility (I''m guessing that''s what `u` in the code above is...) you could skip the group: sum = u.utility_data.sum(:ami_residential) This returns a single number, the sum for all the related records. --Matt Jones -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/11bc5cd1-0594-4fef-8e33-7f8aae0432fb%40googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
Matt:> If you just want data rolled up for *one* Utility (I''m > guessing that''s what `u` in the code above is...) > you could skip the group: > > sum = u.utility_data.sum(:ami_residential)You are spot on: in this case I''m just considering one Utility at a time. I don''t know what possessed me to include the GROUP clause since the WHERE clause alone is sufficient. Time to go back to SQL camp... Many thanks. - ff -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/35f183fb85fb45b7d7c3e1e8aad03da3%40ruby-forum.com. For more options, visit https://groups.google.com/groups/opt_out.