I spend hours trying to solve this, but I can't.
Well, I simply have a table call invoices
I have data like this
I want to know each customer rank by their total sum
How can I archive this?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
You can use a variable for this purpose.
select customer_id, total, rank from ( select customer_id, total, @rank := @rank + 1 as rank from ( select customer_id, sum(total) total from invoices group by customer_id order by sum(total) desc ) t1, (select @rank := 0) t2 ) t3 where customer_id = 1;customer_id | total | rank ----------: | ----: | ---: 1 | 14080 | 2