Rails 3 Sum Product of Two Fields

Rails 3 Sum Product of two fields

Yep :

Room.where(hotel_id: 8).sum("sqft * quantity")

Sum of multiplied columns in Rails

http://ar.rubyonrails.org/classes/ActiveRecord/Calculations/ClassMethods.html

Rails: Order by sum of two columns

You can try this:

Photo.order('full_downloads + presentation_downloads')

It will run this SQL query:

SELECT "photos".* FROM "photos" ORDER BY full_downloads + presentation_downloads

This is potentially slow though. If you have a large dataset and use this sort order often, you should consider creating a total_downloads column and recalculating its value if the record's full_downloads or presentation_downloads column changes.

Is there a simple way to calculate sumproduct in Rails?

avg_price = positions.sum("transaction_price * (volume/#{total_volume.to_f})")

to_f is missing, converting to float to get a decimal to work with.

Example

irb(main):022:0> Position.all
  Position Load (0.3ms)  SELECT "positions".* FROM "positions" LIMIT ?  [["LIMIT", 11]]
=> #<ActiveRecord::Relation [#<Position id: 1, transaction_price: 0.5e2, volume: 150, position_group_id: 1>, #<Position id: 2, transaction_price: 0.1e1, volume: 50, position_group_id: 1>]>
irb(main):023:0> Position.all.sum("transaction_price * (volume/#{total_volume.to_f})")
   (0.2ms)  SELECT SUM(transaction_price * (volume/200.0)) FROM "positions"
=> 37.75

Group and sum 2 columns in rails activerecord

This is what you want:

  Order.joins(cart: { cart_items: :product })
      .group("products.name")
      .select("products.name as product_name", "SUM(cart_items.amount) as cart_items_amount")
      .map { |order| { name: order.product_name, amount: order.cart_items_amount } }

How can I divide sum for a of two column for a certain condition in rails

This should work

Subject.where(semester_id: params[:semester]).pluck("sum(cxgpa) / sum(credit)")

We should write hash as { key: value } instead of using => and you don't need to have subjects in your sum clause cause you are working on only 1 table

Tidyverse solution for rowise sum of products over multiple columns

To obtain a completely generalizable and robust solution, I think it's best to transform the data frame to something more amenable to the task in hand.

df %>% 
  mutate(row=row_number()) %>% 
  pivot_longer(
    -row, 
    names_sep="_", 
    names_to=c("name", "index")
  ) %>%  
  group_by(row, index) %>% 
  pivot_wider(names_from=name, values_from=value)
# A tibble: 6 x 4
# Groups:   row, index [6]
    row index     x     y
  <int> <chr> <dbl> <dbl>
1     1 0         5     3
2     1 1         9     3
3     1 2         2     1
4     2 0         6     2
5     2 1         1     2
6     2 2         1     3

Then calculate the sum of products...

df %>% 
  mutate(row=row_number()) %>% 
  pivot_longer(
    -row, 
    names_sep="_", 
    names_to=c("name", "index")
  ) %>%  
  group_by(row, index) %>% 
  pivot_wider(names_from=name, values_from=value) %>% 
  mutate(product=x * y) %>% 
  group_by(row) %>% 
  summarise(sum_product=sum(product))
# A tibble: 2 x 2
    row sum_product
  <int>       <dbl>
1     1          44
2     2          17

This is robust to the number of rows, the number of variable types (eg x, y and z) and the number of indices (eg 1, 2 and 3).

Edit

My claim that the solution above is robust respect to number of variable types is false. (Because of the stage in the pipe that reads mutate(product=x * y).) Here's a solution that is, together with a modified input dataset to demonstrate that it is.

df1 <- tibble(
  x_0 = c(5,6,1,-1), x_1 = c(9,1,1,3), x_2 = c(2,1,3,4),
  y_0 = c(3,2,1, 2), y_1 = c(3,2,2,2), y_2 = c(1,3,2,2),
  z_0 = c(4,5,1, 3), z_1 = c(3,1,2,1), z_2 = c(2,2,1,3)

)

df1 %>% 
  mutate(row=row_number()) %>% 
  pivot_longer(
    -row, 
    names_sep="_", 
    names_to=c("name", "index")
  ) %>%  
  group_by(row, index) %>% 
  pivot_wider(names_from=name, values_from=value) %>% 
  group_map(
    function(.x, .y, .keep=TRUE) {
      .y %>% bind_cols(.x %>% mutate(product = unlist(apply(.x, 1, prod))))
    }
  ) %>% bind_rows() %>% 
  group_by(row) %>% 
  summarise(sum_product=sum(product))
# A tibble: 4 x 2
    row sum_product
  <int>       <dbl>
1     1         145
2     2          68
3     3          11
4     4          24

Rails Method To Sum Based on Criteria

It looks like the app is looking for columns named product.id and product.msrp as opposed to using the actual values of product.id and product.msrp when it queries the SQL table.

Exactly. The correct way to pass values to the query would be

 where("line_items.order_id IS NOT NULL AND line_items.product_id = ? AND line_items.product_price = ?", product.id, product.msrp)

---

Related Topics