Count records, average and ranking record

Question

i know this Q has a lot of threads, but the answers's a little bit different from what i want, i dont know much 'bout sql so dont know how to change their query as i want

i have a table like this, user will vote and DB record product with point

 id | name | point
 1  | foo  | 8
 2  | bar  | 6
 2  | bar  | 9
 2  | bar  | 9
 1  | foo  | 5

now i want a query that show a result like this

 id | name | AVG point | votes | rank
 1  | foo  |    7.5    |  2    |  2
 2  | bar  |     8     |  3    |  1

AVG point col is average the point each id

vote is total record each id

rank base on number of vote

point column i gonna use later, that why rank column not base on it

thanks in advance ＾へ＾

So instead of learning more about sql by reading the other answers and figuring out how to combine them, you expect us to do it for you? — Barmar, Oct 17 '14 at 03:02
[this question](http://stackoverflow.com/questions/3333665/mysql-rank-function/3333697#3333697) explains how to calculate rank in MySQL. Just write a subquery that calculates the average points and vote count using `GROUP BY id ORDER BY votes DESC`, and plug that into the rank query. — Barmar, Oct 17 '14 at 03:06
If you can't get it working, post your attempted solution and we'll help you fix it. But you have to show some attempt of your own, not just beg for a full solution. — Barmar, Oct 17 '14 at 03:06
@JohnRuddell votes column, i already said above. votes is number of record each id. the more record id have, the higher rank id get — Ōkami X Oukarin, Oct 17 '14 at 03:44

Luc Berthiaume · Answer 1 · 2014-10-17T03:22:34.563

1

You have to use aggregator, you need average aggregator, count aggregator and finally @curRow := @curRow + 1 AS row_number for your rank.

To use aggregator, you need a group by clause.

Select Id,name, avg(point), count(point), @curRow := @curRow + 1 AS row_number
from table
group by Id, name
order by avg(point) desc

edited Oct 17 '14 at 03:22

answered Oct 17 '14 at 03:07

Luc Berthiaume

316
1
9

row_number is just a column name, not func ._. – Ōkami X Oukarin Oct 17 '14 at 03:20

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

the issue with this for getting your rank is you have to order it before counting variables to get it in the correct order.. aka largest votes has the smallest rank

SELECT id, point_avg, votes, rank
FROM
(   SELECT *, @A:= @A + 1 AS rank
    FROM
    (   SELECT id, AVG(point) AS point_avg, COUNT(*) AS votes
        FROM votes
        GROUP BY id
        ORDER BY COUNT(*) DESC
    )t, (SELECT @A:= 0)temp
)t1
ORDER BY id

Fiddle Demo

NOTE:

your expected results have the incorrect average for id 1 it should be 6.5 not 7.5

OUTPUT:

+----+-----------+-------+------+
| ID | POINT_AVG | VOTES | RANK |
+----+-----------+-------+------+
|  1 | 6.5       |     2 |    2 |
|  2 | 8         |     3 |    1 |
+----+-----------+-------+------+

thanks, work as i expect. take my like – Ōkami X Oukarin Oct 17 '14 at 04:55 — Ōkami X Oukarin, Oct 17 '14 at 04:55

Count records, average and ranking record

2 Answers2

NOTE:

OUTPUT: