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

[kra1978]

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

[Barand]

try moving "AND mc.archivecatpkid=1" from the WHERE clause to the join condition for the member category table (as it is a condition on a left-joined table)

[kra1978]

Thank you for your reply but the thing you mentioned doesn't work. Please help further if possible.

[Barand]

include the AVG and the COUNT in the SELECT clause so at least you see the calculated values. Don't use *, select the columns you need.

[kra1978]

include the AVG and the COUNT in the SELECT clause so at least you see the calculated values. Don't use *, select the columns you need.

 

Thank you for your reply. But this is also not working.

 

[Barand]

I didn't expect it to cure it but it might give you a clue why you get the resullt you do. When you join tables with multiple matching rows you get a multiplying effect of the number or rows. eg if 2 rows in one table match 3 rows in the other you get 6 rows returned - which will affect the counts.

[Barand]

try this

SELECT m.*,mc.*,x.avg, x.count
FROM t_member m
LEFT JOIN tr_member_category mc ON m.memberpkid=mc.memberpkid
    AND mc.archivecatpkid=1
LEFT JOIN (
    SELECT memberpkid, AVG(ratingvalue) as avg, COUNT(ratingvalue) as count
    FROM tr_comment
    WHERE approved='YES' AND visible='YES'  
    GROUP BY memberpkid
) as x ON m.memberpkid = x.memberpkid
WHERE m.visible='YES' AND m.approved='YES'
AND m.gender='FEMALE' 
GROUP BY m.memberpkid
ORDER BY x.avg DESC, x.count DESC

[kra1978]

try this

SELECT m.*,mc.*,x.avg, x.count
FROM t_member m
LEFT JOIN tr_member_category mc ON m.memberpkid=mc.memberpkid
    AND mc.archivecatpkid=1
LEFT JOIN (
    SELECT memberpkid, AVG(ratingvalue) as avg, COUNT(ratingvalue) as count
    FROM tr_comment
    WHERE approved='YES' AND visible='YES'  
    GROUP BY memberpkid
) as x ON m.memberpkid = x.memberpkid
WHERE m.visible='YES' AND m.approved='YES'
AND m.gender='FEMALE' 
GROUP BY m.memberpkid
ORDER BY x.avg DESC, x.count DESC

 

Mr. Barand

 

I really thank you very much for your effective help. It works now properly.