1

This question already has an answer here:

I spend hours trying to solve this, but I can't.

Well, I simply have a table call invoices

I have data like this

enter image description here

I want to know each customer rank by their total sum

How can I archive this?

marked as duplicate by RDFozz, SqlWorldWide, mustaccio, hot2use, Mark Sinkinson Mar 8 '18 at 13:24

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 4
    Nothing against McNets ' solution below, but you can find a number of answers to this basic question on the site, simply by searching for MySQL rank. This one includes a number of possible options, depending on how you want to handle customers whose sums come out to be the same. I'm voting to close this question as a duplicate of the one to which I've linked. – RDFozz Mar 6 '18 at 20:04
  • Actually, I have read lot of those but it did't answer my need. I just want to get a single rank value from a list rank value. When I tried those solutions, it is not exactly what I want. – breeze Mar 6 '18 at 20:22
1

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

dbfiddle here

  • This is what I was looking for. However, if I want to get the rank of customer_id = 1 which should return 2, how can I do that? I try to "where customer_id = 1" after your command but it seems not working since it returns 1 instead – breeze Mar 6 '18 at 20:15
  • Done but it's a lot of work, worth it? – McNets Mar 6 '18 at 20:20
  • This is exactly what I wanted. Thank you very very much. – breeze Mar 6 '18 at 20:25

Not the answer you're looking for? Browse other questions tagged or ask your own question.