Group by with counts

Hi all,

I have the following data columns in my table:

occurred_at, name
2012-01-01, Ryan
2012-01-01, Ryan
2012-01-01, Mark
2012-01-01, Ryan
2012-01-01, Paul
2012-01-01, John
2012-02-01, Ryan
2012-02-01, Mark
2012-02-01, Ryan
2012-02-01, Mark
2012-02-01, Paul
2012-02-01, Kevin
2012-02-01, John
2012-03-01, Ryan
2012-03-01, Gary
2012-03-01, Ryan
2012-03-01, Mark
2012-03-01, Paul
2012-03-01, Kevin
2012-01-01, John

What I’m trying to do is Group the names by date and add a count and
output this to a json

{ date: 2012-01-01, ryan: 3, mark: 1, paul: 1, john: 1 },
{ date: 2012-02-01, ryan: 2, mark: 2, paul: 1, john: 1, kevin: 1 },
{ date: 2012-03-01, ryan: 2, mark: 1, paul: 1, john: 1, kevin: 1 }

I have the following code so far:

user controller

def index
@users = User.show_data
render :json
end

user model

def self.show_data(start = 14.months.ago)
total_users = users_by_month(start)
(start.to_date…Date.today).map do |date|
{
occurred_at: date,
total_users[date].name.to_sym: total_users[count],
}
end
end

def self.users_by_month(start)
users = where(occurred_at: start.beginning_of_day…Time.zone.now )
users = users.group(“date(occurred_at)”)
users = orders.select(“occurred_at, count(name) as user_name”)
users.each_with_object({}) do |user, names|
names[user.occurred_at.to_date] = user.user_name
end
end

Thanks in advance,

Ryan

On Monday, February 25, 2013 5:49:56 PM UTC, Ruby-Forum.com User wrote:

I think you’re overcomplicating a little - if you do
User.where(…).group(‘date(occurred_at), name’).count then you should
get
back a hash of where the keys are pairs and the values are
the
counts

Fred

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs