3

I've looked at resources to know how to find the average with RoR built-in average ActiveRecord::Calculations. I've also looked online for ideas on how to calculate averages: Rails calculate and display average.

But cannot find any reference on how to calculate the average of a set of elements from the database column.

In the controller: 

@jobpostings = Jobposting.all

@medical = @jobpostings.where("title like ? OR title like ?", "%MEDICAL SPECIALIST%", "%MEDICAL EXAMINER%").limit(4).order('max_salary DESC')
@medical_salary = "%.2f" % @medical.average(:max_salary).truncate(2)

@medical returns: 

CITY MEDICAL SPECIALIST
220480.0
CITY MEDICAL EXAMINER (OCME)
180000.0
CITY MEDICAL SPECIALIST
158080.0
CITY MEDICAL SPECIALIST
130000.0

I want to find the average of :max_salary (each one is listed correctly under the job title). I use @medical_salary = "%.2f" % @medical.average(:max_salary).truncate(2) to convert the BigDecimal number and find the average of :max_salary from @medical which I thought would be limited to the top 4 displayed above.

But the result returned is: 72322.33, which is the average of the entire column (I checked), instead of the top 4.

Do I need to add another condition? Why does the average of @medical return the average of the entire column?

Any insight would help. Thanks.

Community
  • 1
  • 1
teresa
  • 356
  • 5
  • 21
  • Can you share the SQL queries being run to obtain your results? Also, might be worth looking at `having` vs `where` (at a glance, I'm not sure it's relevant, but I don't fully grok your issue yet): http://stackoverflow.com/questions/9253244/sql-having-vs-where – coreyward Aug 22 '16 at 19:53

2 Answers2

2

@medical.average(:max_salary) is expanded as @jobpostings.where(...).average(:max_salary).limit(4) even though the limit(4) appears previously in the method chain.

You can confirm this by checking the query that is run which be as follows:

SELECT  AVG(`jobpostings `.`max_salary `) FROM `tickets` ... LIMIT 4`

Effectively, LIMIT 4 doesn't do anything because there is only one average number in the result of the above query.

One way to accomplish what you are trying to do will be:

$ @top_salaries = @jobpostings.where("title like ? OR title like ?", "%MEDICAL SPECIALIST%", "%MEDICAL EXAMINER%").limit(4).map(&:max_salary)
=> [ 220480.0, 180000.0, 158080.0, 130000.0]
$  average = @top_salaries.reduce(:+) / @top_salaries.size
=> 172140.0
Prakash Murthy
  • 12,923
  • 3
  • 46
  • 74
  • Thank you for your answer and explaining why the LIMIT 4 doesn't do anything. I tried the code and it works, I just added a order by so it was in descending order of max_salary value. `@medical_salaries = @jobpostings.where("title like ? OR title like ?", "%MEDICAL SPECIALIST%", "%MEDICAL EXAMINER%").order('max_salary DESC').limit(4).map(&:max_salary)` – teresa Aug 24 '16 at 21:23
1
@medical.average(:max_salary)

This line corresponds to following MySQL query:

SELECT AVG(`jobpostings`.`max_salary`) AS avg_max_salary FROM `jobpostings` WHERE (`jobpostings`.`title` like "%MEDICAL SPECIALIST%" OR title like "%MEDICAL EXAMINER%") LIMIT 4

Since MySQL already calculated AVG on the column max_salary, it returns only 1 row. LIMIT 4 clause doesn't actually come into play.

You can try following:

@limit = 4
@medical = @jobpostings.where("title like ? OR title like ?", "%MEDICAL SPECIALIST%", "%MEDICAL EXAMINER%").limit(@limit).order('max_salary DESC')
@medical_salary = "%.2f" %(@medical.pluck(:max_salary).reduce(&:+)/@limit.to_f).truncate(2)

It will even work for cases when no. of results are 0.

Utsav Kesharwani
  • 1,715
  • 11
  • 25
  • Thank you for explaining why the `LIMIT 4` doesn't work. I have seen the `pluck` method before so I'll be looking at that. But I'm trying the code and getting a `wrong number of arguments (given 1, expected 0)` error. – teresa Aug 24 '16 at 21:26