CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3postgresqlactiverecord
user411103
user411103

Reputation:

ActiveRecord query that groups by ID and sums rows

My Postgresql DB has this structure:

TABLE Orders
id (string)
userId (string)
prodId (string)
value (integer)

This is an example of data:

id      userId      prodId    value
1       [email protected]     prod1     5
2       [email protected]     prod1     -1
3       [email protected]     prod1     -4
4       [email protected]     prod2     9

I want to do a query from ActiveRecord that sums all the values for a specific userId, so the query for ([email protected]) would return a LIST like this:

prod1    1
prod2    9

My first approach is this one, but it doesn't work:

orderList = Orders.select("SUM(orders.amount) AS num_prods").where((:userId => HERE_USER_ID).group(:prodId)

EDIT: rephrased thanks to feedback

Upvotes: 0

Views: 3914

Answers (1)

Mohammad AbuShady
Mohammad AbuShady

Reputation: 42869

Order.where(userId: id).group(:prodId).sum(:value) # replace `:id` with your value

This should give you a hash, like so

{1=>10, 2=>20, 5=>20}

the keys 1,2,5 represent the product id, and the values 10,20,20 represent the sum values.

Upvotes: 5

Related Questions