group by + sum

1102 views
Skip to first unread message

Werner

unread,
Jan 24, 2013, 3:35:02 AM1/24/13
to rubyonra...@googlegroups.com
Hi.. I need some support...

table:
week_id, user_id, project_id, hours
ex. =>
33, 2, 1, 10
34, 2,1,15
33, 2, 2, 20
35, 3, 1,20
etc.

Want to display a sum of hours per week_id per user_id
I have:

@hours = HourUser.includes(:user).group_by { |h| h.week_id }

@hours.keys.sort.each do |hour|
@hours[hour].collect(&:stunden).sum

Hours are summed up, but not sorted by user_id..
How to get that?

Thanks
Werner





Jordon Bedwell

unread,
Jan 24, 2013, 3:49:33 AM1/24/13
to rubyonra...@googlegroups.com
Hash#sort_by

Werner

unread,
Jan 24, 2013, 4:14:53 AM1/24/13
to rubyonra...@googlegroups.com
Feel a litte ittle stupid right now..
where to sort_by?

Jordon Bedwell

unread,
Jan 24, 2013, 4:24:57 AM1/24/13
to rubyonra...@googlegroups.com
On Thu, Jan 24, 2013 at 3:14 AM, Werner <webagent...@googlemail.com> wrote:
> Feel a litte ittle stupid right now..
> where to sort_by?

Well, without a coherent though (AKA proper data outputs in the form
of say a copy of the pry session where it outputs => { } and then you
go through the steps I cannot say. I have no idea about your code and
your examples are pretty broad as to the data you are working with.

Werner

unread,
Jan 24, 2013, 4:58:46 AM1/24/13
to rubyonra...@googlegroups.com
Think, you mean something like:

Model.sort_by{|h| [h.user_id, -h.week_id]}
?

Jim Ruther Nill

unread,
Jan 24, 2013, 5:36:44 AM1/24/13
to rubyonra...@googlegroups.com
On Thu, Jan 24, 2013 at 4:35 PM, Werner <webagent...@googlemail.com> wrote:
Hi.. I need some support...

table:
week_id, user_id, project_id, hours
ex. =>
33, 2, 1, 10
34, 2,1,15
33, 2, 2, 20
35, 3, 1,20
etc.

Want to display a sum of hours per week_id per user_id
I have:

@hours = HourUser.includes(:user).group_by { |h| h.week_id }

@hours.keys.sort.each do |hour|
@hours[hour].collect(&:stunden).sum


sums = HourUser.sum(:hours, group: [:week_id, :user_id], order: :user_id)

You'll end up with something like [33, 2] => 70, [34, 2] => 15, [35, 3] => 20
Just remember that to get a certain value, you'll have to pass an array as the index
ie sums[[33,2]] to get 70

 

Hours are summed up, but not sorted by user_id..
How to get that?

Thanks
Werner





--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To post to this group, send email to rubyonra...@googlegroups.com.
To unsubscribe from this group, send email to rubyonrails-ta...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msg/rubyonrails-talk/-/nIwEcQd5RUMJ.
For more options, visit https://groups.google.com/groups/opt_out.
 
 



--
-------------------------------------------------------------
visit my blog at http://jimlabs.heroku.com

Werner

unread,
Jan 24, 2013, 6:55:44 AM1/24/13
to rubyonra...@googlegroups.com
Hi Jim.. thanks so far..

in the moment this is a bit too far for me.


Just remember that to get a certain value, you'll have to pass an array as the index
ie sums[[33,2]] to get 70
=> this is unclear
Pls. be so kind to explain the view part.

Werner

Jim Ruther Nill

unread,
Jan 24, 2013, 8:16:16 AM1/24/13
to rubyonra...@googlegroups.com
On Thu, Jan 24, 2013 at 7:55 PM, Werner <webagent...@googlemail.com> wrote:
Hi Jim.. thanks so far..

in the moment this is a bit too far for me.


Just remember that to get a certain value, you'll have to pass an array as the index
ie sums[[33,2]] to get 70
=> this is unclear
Pls. be so kind to explain the view part.

since the keys of the hash is an array, you need to use an array as the index to get a value

>> sums = { [33, 2] => 70, [34, 2] => 15, [35, 3] => 20 }
>> sums[34,2]
ArgumentError: wrong number of arguments (2 for 1)
from (irb):3:in `[]'
from (irb):3
>> sums[[34,2]] # 15

the keys are defined by the group option you passed to #sum, so if you pass as sql statement to
the group option, you'll get that as key. ie (postgre)

>> HourUser.sum(:hours, group: "week_id || ' --- ' || user_id", order: :user_id)
>> { '33 --- 2' => 70, '34 --- 2' => 15, '35 --- 3' => 20 }

hope this helps

 
To view this discussion on the web visit https://groups.google.com/d/msg/rubyonrails-talk/-/CDisLJSEMNEJ.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

Werner

unread,
Jan 24, 2013, 9:38:49 AM1/24/13
to rubyonra...@googlegroups.com
Jim..sorry.. not my day...
have to contemplate about your solution.

I wonder..

HourUser.includes(:user).where.....group_by { |h| h.week_id }

<% @hours.keys.sort.each do |h| %>
 <%= @hours[h].collect(&:hour).sum %></td>
<% end %>

is giving me what I want, just needs to be grouped by user_id

Thanks so far.

Jim Ruther Nill

unread,
Jan 24, 2013, 10:01:06 AM1/24/13
to rubyonra...@googlegroups.com
On Thu, Jan 24, 2013 at 10:38 PM, Werner <webagent...@googlemail.com> wrote:
Jim..sorry.. not my day...
have to contemplate about your solution.

I wonder..

HourUser.includes(:user).where.....group_by { |h| h.week_id }

<% @hours.keys.sort.each do |h| %>
 <%= @hours[h].collect(&:hour).sum %></td>
<% end %>

is giving me what I want, just needs to be grouped by user_id

Thanks so far.

So given the current solution you have, you also want to group by user_id right?
so here's how it should go.

@hour_users = HourUser.all.group_by(&:week_id)

gives you a hash with week_ids as keys

@hour_users.each do |week_id, by_week|
  by_week.group_by(&:user_id).each do |user_id, hour_users|
    hour_users.map(&:hour).sum
  end
end

using my first suggestion

@hour_users = HourUser.sum(:hour, group: [:week_id, :user_id])

@hour_users.each do |(week_id, user_id), total_hours|
  # do something with week_id, user_id and total_hours
end

Good luck! 
To view this discussion on the web visit https://groups.google.com/d/msg/rubyonrails-talk/-/rs-cau_8-hcJ.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

Werner

unread,
Jan 25, 2013, 8:42:29 AM1/25/13
to rubyonra...@googlegroups.com
Well.. I cant get it working

Try to explain it again..

db_table:
week_id, project_id, user_id, hour
ex. =>
33, 1, 1, 10
33, 4, 1, 20
33, 1, 2, 0
34, 1, 2, 15
34, 1, 1, 0


So, user with the id 1 worked 10 hours in week 33 and 20 in the week 33, but other project
I want to show all hours summed up per week per user
row1(user1) => week 33 => 30, week 34 => 0
row2(user2) => week 33 => 0, week 34 => 15

Step one:
I want one row per user, so I group:
@hours = HourUser.all.group_by(&:user_id)

view:
@hours.each do |user, weeks|

gives me each user in one <tr>
within this row, each week one cell

<% weeks.group_by(&:week_id).each do |week, hours| %>
<td><%= hours.map(&:hour).sum %>

Shows the hours but not summed up. Instead I get :
row 1 =>  ...10.. and does not stop the row but starts again with ... 20...
row 2 =>  ...0 15

How to? Do I also have to group otherwise..?
Thanks for support.

Jim Ruther Nill

unread,
Jan 25, 2013, 8:50:53 AM1/25/13
to rubyonra...@googlegroups.com
so this is your remaining problem right?  I think the hour column is
a string which results to a concat of the values instead of simple
addition.  try hours.map { |h| h.hour.to_f }.sum
 
To view this discussion on the web visit https://groups.google.com/d/msg/rubyonrails-talk/-/uOA60u2KRusJ.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

Werner

unread,
Jan 25, 2013, 11:00:12 AM1/25/13
to rubyonra...@googlegroups.com
Hi Jim..
thanks..
looks good now.. I had a mistake in another model..so the grouping was not correct.

my god.. what a trip sometimes..

Werner
Reply all
Reply to author
Forward
0 new messages