SELECT C.CountryName, SUM(ABS(IR1.RelevantRank - CA.Amplitude)) AS DifferenceRank
FROM questions_CountriesAmplitudes AS CA JOIN
(SELECT SUM(QA.Amplitude * ANS.AnswerMultiplier) AS RelevantRank, QA.CategoryID AS CategoryID
FROM questions_UserAnswers AS UA JOIN questions_Answers AS ANS JOIN questions_QuestionAmplitudes AS QA
ON QA.QuestionID=ANS.QuestionID AND ANS.QuestionID=UA.QuestionID AND UA.AnswerID=ANS.AnswerID
WHERE UA.UserUUID=1
GROUP BY CategoryID) AS IR1 JOIN questions_Countries AS C
ON CA.CategoryID=IR1.CategoryID AND CA.CountryID=C.CountryID
GROUP BY CA.CountryID
ORDER BY DifferenceRank;
"(SELECT QuestionID, QuestionBody, EffectiveAmp FROM "+
" (SELECT Q.QuestionID, Q.QuestionBody, 1-ABS(A.Amplitude) AS EffectiveAmp "+
" FROM questions_Questions AS Q LEFT JOIN questions_QuestionAmplitudes as A "+
" ON Q.QuestionID=A.QuestionID "+
" WHERE A.CategoryID=_CatID_ "+
" ORDER BY EffectiveAmp DESC) AS T1) "+
"UNION "+
"(SELECT QuestionID, QuestionBody, EffectiveAmp FROM "+
" (SELECT Q.QuestionID, Q.QuestionBody, 1 AS EffectiveAmp "+
" FROM questions_Questions AS Q "+
" WHERE NOT EXISTS "+
" (SELECT Aq.QuestionID, Aq.Amplitude "+
" FROM questions_QuestionAmplitudes as Aq "+
" WHERE Aq.QuestionID=Q.QuestionID && Aq.CategoryID=_CatID_) "+
" ORDER BY rand()) AS T2) "+
"LIMIT _Limit_; ";