Show number of row in mysql select

Question

I have this statement:

SELECT count(s.name), s.name, avg(a.rate),  @curRank := @curRank + 1 AS rank 
FROM `avatec_objects_comments` as a, avatec_objects as s, (SELECT @curRank := 0) r
WHERE a.oid =s.id and s.status=0 
GROUP BY s.name 
ORDER BY  avg(a.rate) desc, count(s.name) desc

This is my desired result (see numbers on side)

How can I set the rank like in the desired result above based on count(s.name) and avg(a.rate)?

Answer 1

MySQL seems to have an issue with using variables with group by. The solution is to use a subquery. In addition, your query can be improved by using explicit join syntax:

SELECT cnt, name, avgrate,
       (@curRank := @curRank + 1) AS rank 
FROM (SELECT count(o.name) as cnt, o.name, avg(oc.rate) as avgrate, 
      FROM avatec_objects o JOIN
           avatec_objects_comments oc
           ON oc.oid = o.id 
      WHERE o.status = 0
      GROUP BY o.name
     ) oc CROSS JOIN
     (SELECT @curRank := 0) vars
ORDER BY  avgrate desc, cnt desc;