Show number of rows in mysql select
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 output (see numbers on the side)
How do I set the rank as in the desired result above, based on count(s.name)
and avg(a.rate)
?
source to share
It seems that MySQL has a problem using variables with group by
. The solution is to use a subquery. Also, your query can be improved with an explicit syntax join
:
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;
source to share
You need to attach your request. The order will be executed as the last operation in this request, so it will always replace your rows. Please check below:
SET @rank=0;
SELECT t1.*, @rank:=@rank+1 AS rank FROM
(SELECT count(s.name), s.name, avg(a.rate)
FROM `avatec_objects_comments` as a, avatec_objects as s
WHERE a.oid =s.id and s.status=0
GROUP BY s.name
ORDER BY avg(a.rate) desc, count(s.name) desc)) t1;
In this query, you will get the required data (it will be selected, grouped and ten ordered) and then it will simply add ranks to the data ready to be read.
source to share