MySQL sorts on average by two average values

Question

I work on the contest website, where there are two types of users, ordinary site participants and judges. Everyone can use the drag and drop tool to order entries in a particular contest in the order in which they choose. When they are done, the corresponding login identifiers will be tied to the ranking, which can then be used to determine which entry in the contest received the highest average score. The winner will actually be determined by averaging the average values ​​for each group.

I hope that in fact I get a table indicating EVERY record in a particular competition with a heading, and then I show 3 values, avg_normal for this record, avg_judge for this record, and then these two values ​​are added together and divided by two, therefore avg_normal and avg_judge each account account for 50% of avg_all. Finally, sort the table by avg_all.

avg_all = ((avg_normal + avg_judge) / 2)

They order entry_ids 1, 2, 3, 4, 5 in order. The ranking value starts from zero like this:

entry_id, entry_ranking, author_id 1, 0, 1 2, 1, 1 3, 2, 1 4, 3, 1 5, 4, 1 

I hope to determine the average values ​​on a scale from 1 to 100, so the input rating is 0 = 100 points, 1 = 90, 2 = 80, 3 = 70 and something higher than 4 = 5 points

Each user joins a group in a different table, so they are either a regular user or a judge

I want to write a query that finds

1.) Average user rating NORMAL

2.) Average JUDGE user rating

3.) The average of NORMAL and JUDGE SCORE.

Thus, average average user = 93.3333, average judge = 70, total average = 81.66665

Thanks to the answers below, both requests work like a champion.

Answer 1

Please note the following:

• I suggested that the elements have a user_type field that stores either "NORMAL" or "JUDGE"

• I removed the attachment to the data and the .title caption group because I don’t see how they relate to your averages.

.

 SELECT t.title, AVG(CASE WHEN user_type = 'NORMAL' THEN IF (r.ranking_value = '0', 100, 0) + IF (r.ranking_value = '1', 90, 0) + IF (r.ranking_value = '2', 80, 0) + IF (r.ranking_value = '3', 70, 0) + IF (r.ranking_value = '4', 5, 0) END) AS avg_normal, AVG(CASE WHEN user_type = 'JUDGE' THEN IF (r.ranking_value = '0', 100, 0) + IF (r.ranking_value = '1', 90, 0) + IF (r.ranking_value = '2', 80, 0) + IF (r.ranking_value = '3', 70, 0) + IF (r.ranking_value = '4', 5, 0) END) AS avg_judge, (AVG(CASE WHEN user_type = 'NORMAL' THEN IF (r.ranking_value = '0', 100, 0) + IF (r.ranking_value = '1', 90, 0) + IF (r.ranking_value = '2', 80, 0) + IF (r.ranking_value = '3', 70, 0) + IF (r.ranking_value = '4', 5, 0) END) + AVG(CASE WHEN user_type = 'JUDGE' THEN IF (r.ranking_value = '0', 100, 0) + IF (r.ranking_value = '1', 90, 0) + IF (r.ranking_value = '2', 80, 0) + IF (r.ranking_value = '3', 70, 0) + IF (r.ranking_value = '4', 5, 0) END)) / 2 AS avg_all FROM rankings r LEFT JOIN titles t ON r.entry_id = t.entry_id LEFT JOIN members m ON t.author_id = m.member_id WHERE r.contest_id IN ('CONTEST ID NUMBER') GROUP BY t.title ORDER BY avg_all; 

Answer 2

All this change wraps the original request, lines 314159 have the bulk of the work

  SELECT aa.title, aa.avg_normal, aa.avg_judge, (aa.avg_normal + aa.avg_judge) / 2 AS avg_all
 from 
 (
 SELECT
 t.title, 
 AVG (CASE WHEN group_id = '6' THEN IF (r.ranking_value = '0', 100, 0) + IF (r.ranking_value = '1', 90, 0) + IF (r.ranking_value = '2', 80, 0) + IF (r.ranking_value = '3', 70, 0) + IF (r.ranking_value> = '4', 5, 0) END) AS avg_normal,
 AVG (CASE WHEN group_id = '7' THEN IF (r.ranking_value = '0', 100, 0) + IF (r.ranking_value = '1', 90, 0) + IF (r.ranking_value = '2', 80, 0) + IF (r.ranking_value = '3', 70, 0) + IF (r.ranking_value> = '4', 5, 0) END) AS avg_judge
 FROM exp_rankings r
 LEFT JOIN exp_weblog_titles t
   ON r.entry_id = t.entry_id
 LEFT JOIN exp_members m
   ON t.author_id = m.member_id
 WHERE r.contest_id IN ('22')
 GROUP BY
   t.title
 ORDER BY
  avg_all) as aa;