MySQL sort by average of two averages
العربية
български
català
中文
čeština
dansk
Nederlands
eesti
suomi
français
Deutsch
Ελληνικά
עברית
हिंदी
magyar
Bahasa Indonesia
italiano
日本語
한국어
latviešu
lietuvių
norsk
polski
Português
română
русский
slovenčina
slovenski
español
svenska
ไทย
Türkçe
українська
Tiếng Việt
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 |
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;