Hello Everyone,
I have following query and I am trying to display members in the order where members with highest average rating will be displayed first, if more than one members have same average rating then highest number of rating will be considered.
Here,
Member A has been rated by 3 visitors and average rating value is 5 while Member B has been rated by 2 visitors and average rating value is 5
So according to below query, Member A should display first because he has 5 average rating and rated by 3 persons while Member B should display on second position.
But Member B is displaying first and Member A is displaying second so this is problem. Please let me know what wrong I am doing in query.
SELECT m.,mc.
FROM t_member m
LEFT JOIN tr_member_category mc ON m.memberpkid=mc.memberpkid
LEFT JOIN tr_comment c
ON m.memberpkid=c.memberpkid
AND c.approved=‘YES’ AND c.visible=‘YES’
WHERE m.visible=‘YES’ AND m.approved=‘YES’
AND m.gender=‘FEMALE’ AND mc.archivecatpkid=1
GROUP BY m.memberpkid
ORDER BY avg(c.ratingvalue) DESC, COUNT(c.ratingvalue) DESC
Thank you very much in advance,
KRA
