Column Calculations - better way to rank?

[PHPsites]

Hello.

Can someone help with this? Even an explosion of my current plan would be great as I'm not sold on it!

Here goes....

Employees can choose their top 5 favorite items. Want to display enterprise top 5. However, want to give weight to both highest rank and highest average position. For example, an item can be listed #1 on 5 surveys and not at all on 5 other surveys. Want to weight appropriately so both factors are taken into account - that is averages #1 when listed but also was not listed on 5 lists.

Besides data about each choice , currently have two columns - total points & times_chosen.

Total points - every 1st place choice gets 5 points added to total, 2nd place choice gets 4 points added to total, 3rd->3, 4th->2, 5th->1.

Times_chosen - every time a choice is chosen, times_chosen = times_chosen + 1.

Average Place can be computed by dividing total point by times_chosen.

Can easily do an order by total_point desc in order to rank top to bottom.

Can anyone give an easy way to create a column for this computation?
overall_rank = (total_points place) + (average_place) where total_points place equals the position when ordered desc.

Does this make sense? Obviously it is convoluted and I can't think around this. there has to be an easier way. Any ideas?

I want to pull one query that has the choices ordered by an average of total_points place and average_place position. Any help would be great. Thanks.

[nemonoman]

I think you are indeed complicating the problem. The result of all these calculations appears to be exactly the same as AVERAGE.

[PHPsites]

Thanks for the response nemonoman. In the example I gave, that is correct. However, the real life problem uses ranks of 20. The problem is noticeable if a choice is listed high on a lot of surveys and not at all on others.

Perhaps this would be an easier question. Is there a way that column default value can be a calculation?

For example, column "calc" would have a default of (total_points-100) + (average_rank * 20) or something similar. This is just made up but can something like this happen?

Thanks.

[nemonoman]

MySQL through version 5 doesn't have calculated columns. You might be able to create a stored procedure, but I frankly don't see how. Oracle used to have calculated columns-- probably still does.

You can probably just calculate the rankings dynamically using SQL:

SELECT id, (sum(someValue-100)+sum(Average(someValue/20)) FROM someTable
GROUP BY id, ORDER BY 2 DESC