I want to perform multiple calculations using one query to price_histories table, and finally render some statistics using those prices like average, minimum and maximum etc.
price_histories_controller.rb
price_stats = PriceHistory.where('created_at >= ? AND cast(item_id as integer) = ?', 1.day.ago, params['item_id'])
avg_array = price_stats.group(:day).average(:price).to_a
min_array = price_stats.group(:day).min(:price).to_a
max_array = price_stats.group(:day).max(:price).to_a
count_array = price_stats.group(:day).count(:price).to_a
This is the relevant code that causes the error, i'd like to perform some calculations on a set of grouped data but after the first calculation is done, I keep getting
TypeError (no implicit conversion of Symbol into Integer)
Ideally I would end up with an object like this one to be rendered:
@all_stats = {
average: avg_array,
min: min_array,
max: max_array,
count: count_array
}
render json: @all_stats
This sums up my intentions pretty well, I'm new to ruby and I'd like a solution or a better approach which I'm sure there are.
The following code works fine and I'd like anyone to point me in the right direction to finding out why this works fine and when adding and extra calculation it doesn't:
price_stats = PriceHistory.where('created_at >= ? AND cast(item_id as integer) = ?', 1.day.ago, params['item_id'])
avg_array = price_stats.group(:day).average(:price).to_a
and leads to:
{
"average": [
[
null,
"11666.666666666667"
],
[
"24/4/2019",
"11666.666666666667"
],
[
"24",
"11666.6666666666666667"
],
[
"2051",
"11666.6666666666666667"
]
],
"min": [],
"max": [],
"count": []
}
Other approach:
PriceHistory.select(
"AVG(price) AS average_score,
MIN(price) AS average_min,
MAX(price) AS average_max,
COUNT(*) AS price_count"
).where(
'created_at >= ? AND cast(item_id as integer) = ?',
1.day.ago, params['item_id']
).group(:day)
Error:
ArgumentError (Call `select' with at least one field):
I think this should work:
PriceHistory.where(
'created_at >= ? AND cast(item_id as integer) = ?',
1.day.ago,
params['item_id']
).group(:day).select(
"SUM(price) AS sum_price",
"MAX(price) AS max_price",
"MIN(price) AS min_price",
"AVG(price) AS avg_price",
"day"
)
This will return you an array of records, each which has methods day
, sum_price
, max_price
, min_price
, and avg_price
.
Note that the names of the SQL functions might be different based on your db
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With