This is a simplified version of what I'm trying to do. I am trying to rank users based on how many miles they walked overall.
This data is stored in the table called walks. Every time a user makes a walk, an entry is added.
create temporary table walks
(
id int unsigned auto_increment primary key,
user_id int unsigned not null,
miles_walked float unsigned default '0' not null,
date date not null
);
To fill in the table:
insert into walks (user_id, miles_walked, date)
values
(1, 10.1, '2022-12-20'),
(2, 60.2, '2022-12-21'),
(3, 30.3, '2022-12-22'),
(1, 0.4, '2022-12-23'),
(2, 10.5, '2022-12-24'),
(3, 10.6, '2022-12-25'),
(1, 40.7, '2022-12-26'),
(2, 80.8, '2022-12-27'),
(3, 30.9, '2022-12-28');
select * from walks;
select user_id,
SUM(miles_walked) as miles_walked_total,
ROUND(SUM(miles_walked), 1) as miles_walked_total_rounded,
row_number() over (order by SUM(miles_walked) desc) as miles_rank
from walks
group by user_id
order by user_id
As you can see, rounding is WRONG for users with id 2 and 3. What happened? Like I said, this is a simplified example. In my real case, not just rounding, but the ranking is wrong for the whole set when I use functions like ROUND and LENGTH:
ROW_NUMBER() OVER (ORDER BY (SUM(LENGTH(reports.comments)) + SUM(report_items.report_items_characters_number)) DESC) AS ranking
