Search code examples
mysqlsql-order-bysequence

MYSQL row sequence with SUM() and ORDER BY

I try to order my rows by user's total point.

SUM and ORDER BY working correctly. But also i want to add sequence numbers of rows.

When I try to use @row_number I get some numbers but sequence is incorrect.

enter image description here

correct num column order should be 1,2,3,4 because I use order by total_point of sum of user's points.

How can I get correct sequence for num column?

SELECT 
users.user_id, 
users.user_hash, 
(@row_number:=@row_number + 1) AS num,
sum(total_point) as total_point

FROM (SELECT @row_number:=0) AS t,user_stats
LEFT JOIN users on users.user_id = user_stats.stats_user_id
WHERE create_date BETWEEN "2020-04-01 00:00:00" AND "2020-04-30 23:59:59"
GROUP BY stats_user_id
ORDER BY total_point DESC

v: mysql 5.7

Solution

  • You must use the with total sorted rows and give them a number

    SELECT 
    user_id,
    user_hash,
    users.user_nick,
    (@row_number:=@row_number + 1) AS num,
    total_point
FROM
    (SELECT 
        users.user_id,
        users.user_hash,
        users.user_nick,
        SUM(total_point) AS total_point
    FROM
        user_stats
    LEFT JOIN users ON users.user_id = user_stats.stats_user_id
    WHERE
        create_date BETWEEN '2020-04-01 00:00:00' AND '2020-04-30 23:59:59'
    GROUP BY stats_user_id
    ORDER BY total_point DESC) t1,
    (SELECT @row_number:=0) AS t
ORDER BY num ASC;