# AVG and COUNT functions are not giving proper result in SQL query

**URL:** <https://forum.kirupa.com/t/avg-and-count-functions-are-not-giving-proper-result-in-sql-query/329164>\
**Category:** programming\
**Created:** [July 27, 2012, 9:23am UTC](https://forum.kirupa.com/t/avg-and-count-functions-are-not-giving-proper-result-in-sql-query/329164 "2012-07-27T09:23:10Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![kra123](https://avatars.discourse-cdn.com/v4/letter/k/3bc359/32.png) [@kra123](https://forum.kirupa.com/u/kra123)\
**Post date:** [July 27, 2012, 9:23am UTC](https://forum.kirupa.com/t/avg-and-count-functions-are-not-giving-proper-result-in-sql-query/329164/1 "2012-07-27T09:23:10Z")

</div>

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