MySQL sort by average of two averages

Question

I am working on a contest site where there are two types of users, normal site members, and judges. Each can use a drag and drop tool to order the entries in a particular contest in the order they choose. When they are done the relevant entry ids are attached a ranking value that can then be used to determine which entry in contest got the highest average score. The winner will actually be determined by the averaging the averages of each group.

What I hope to do is end up with a table showing EACH entry in a particular contest, with the title, and then show 3 values, avg_normal for that entry, avg_judge for that entry, and then those two values added together and divided by two, so the avg_normal and avg_judge each account for 50% of the 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 at zero so:

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

I'm hoping to determine the averages on a scale of 1-100, so an entry ranking of 0 = 100 points, 1 = 90, 2 = 80, 3 = 70, and anything above 4 = 5 points

Each user is attached to a group in another table, so they are either a normal user, or a judge

I want to be able to write a query that finds

1.) The average NORMAL user vote score

2.) The average JUDGE user vote score

3.) The average of the NORMAL & JUDGE SCORE.

So Normal User average = 93.3333, Judge average = 70, Total Average = 81.66665

Thanks to the answers below, both queries work like a champ.

Answer 1

Please note the following:

• I've assumed that there is a field user_type in members which stores either 'NORMAL' or 'JUDGE'

• I've removed the join to data and the group by of titles.title because I don't see how they're relevant 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 does it wrap the original query, lines314159 has 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;