We help IT Professionals succeed at work.

Rank by Total Sales - MySQL

sj77
sj77 asked
on
Hello, Experts,

I am writing a SQL query in MySQL (mainly a SQL Server user - getting feet wet in MySQL as of recent) where I am looking to rank total sales (calculated field) from highest to lowest. In SQL Server you were able to do this with the RANK function. Based on my research, I cannot seem to find a decent answer on how to properly accomplish this in MySQL.

Here is what I am after.

Output:

Column Names: StoreID, Rank, Total_Sales
                             TX001, 3, 15000.00
                             AZ005, 1, 60000.00
                             CO010, 2, 22000.00

Currently, here is my working SQL code that I have composed. My output is coming out with ranking being incorrect. I.E: Rank = 1 ===> 15000.00 and Rank 3 ====> 60000.00

set @rank := 0;

select

    s.nickname,
    @rank := @rank + 1 as rank,
    sum(ro.total_sales - ro.taxes) / 100 as sales



from rpt_repair_order ro
inner join shop s
on ro.shop_id = s.id

where  DATE(CONVERT_TZ(ro.posted_date, 'UTC', s.time_zone_id)) > '2020-01-19' and  DATE(CONVERT_TZ(ro.posted_date, 'UTC', s.time_zone_id)) <= '2020-01-25'

group by s.nickname

order by sales asc;

Open in new window

Comment
Watch Question

Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

What version of MySQL?


At least in version8, it has the rank function that should be almost exactly as SQL Server:

https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_rank

Author

Commented:
Version 5.x. We plan to upgrade to version 8 sometime this year. Without considering the version, is this not possible in other words? @slightwv
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:

I don't know.  I'm not a MySQL Expert.


I have to go by Google.


This sees to work:

SELECT    storeid,
          total_sales,
          @curRank := @curRank + 1 AS rank
FROM      tab1 t, (SELECT @curRank := 0) r
order by total_sales desc
;


My test example is here:

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=37192d645eaa7b6f81016cfdeaed1f0d

Tomas Helgi JohannssonDatabase Administrator / Software Engineer
Commented:
Hi,

As sligthwv mentions then RANK function is available as of version 8.0 in MySQL and MariaDB version 10.2.
https://www.mysqltutorial.org/mysql-window-functions/mysql-rank-function/

Using older version you would need to create the rank "manually" in the SQL like shown in this example
https://towardsdatascience.com/mysql-how-to-write-a-query-that-returns-the-top-records-in-a-group-12865695f436

Regards,
    Tomas Helgi
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

>>  like shown in this example


And in the SQL I already posted...

Author

Commented:
@slightwv, so the ranking appears its just not correct. Meaning, that the data being output is off by the ranking. Example: a storeID with $60000 in sales is getting a rank lower than 1 even though it is the highest total sales. Any idea why this would be happening?
Tomas Helgi JohannssonDatabase Administrator / Software Engineer

Commented:
Hi,

Your sql needs to order the rows in the prefered order and use if statements to the prefered search condition to apply the ranking.
The example un the link I  provided in my previous comment gives you an idea how to do this.
In MySQL 8.x the sql syntax and RANK function simplifies this query a lot.

Regards,
   Tomas Helgi

Author

Commented:
I see. So it sounds like there is no easy way to do this other than making sure we get on the latest version of MySQL v8. OK, cool. Thanks, just wanted to make sure I crossed my t's and dotted my i's before I closed this case. Thanks for your help. I will share the solution between you two. You all have been extremely helpful.

Author

Commented:
These gentlemen were outstanding.
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

>> Any idea why this would be happening?                                  


I would need sample data with the results.  In the link I provided store AZ005 with 60,000 is ranked 1.


It appears it can be done pre v8 but not as easy.

Author

Commented:
How would you like me to present the data for you? csv?
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

Something I can create a test case with.


If you are going to do a lot of work, a dbfiddle.uk already set up with tables and data would be perfect.  That way we can just focus on the results and not take the time to convert what you post into a test case.


Then provide the expected results from the fiddle.

Author

Commented:
Here is the query:

select

    ro.shop_id,
    s.nickname,
    sum(ro.total_sales - ro.taxes) / 100 as sales,
    count(ro.repair_order_id) as car_count,
    @curRank := @curRank + 1 as curRank



from rpt_repair_order ro
inner join shop s
on ro.shop_id = s.id
, (SELECT @curRank := 0) r

where  DATE(CONVERT_TZ(ro.posted_date, 'UTC', s.time_zone_id)) > '2020-01-19' and  DATE(CONVERT_TZ(ro.posted_date, 'UTC', s.time_zone_id)) <= '2020-01-25'

group by ro.shop_id

order by sales desc;

Open in new window

Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

I prefer dbfiddle.uk but thanks for that.


Now I need the expected results from the test data you provided.


Using the SQL in the first  fiddle I posted, here is what I have:

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=1ad9f1bc74b8dc6b4d19a6569942907c


SELECT    shopid,
          sales,
          carcount,
          @curRank := @curRank + 1 AS rank
FROM      test t, (SELECT @curRank := 0) r
order by sales desc
;


The results I get are:

shopid
sales
carcount
rank
CO005
45000.0000
111
1
AZ005
36000.0000
118
2
IN007
32000.0000
115
3
TX001
20000.0000
100
4


Author

Commented:
Hi, @slightwv,

I have already implemented your solution and hence my rebuttal that the ranking is lining up. Please review the image attached showing the query and the output. The query is identical to yours with a little more data being gleaned.
experts-e-012020.jpg

Author

Commented:
is not lining up*
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

I can only test against what you provide.


Do you have a sales column in those tables?  That is could be the problem.


I need a test case that shows the problem.  Please provide both tables with sample data that shows the rank not correct using the SQL you provide.



Author

Commented:
No, the sales column in the output is a calculated field sum(ro.total_sales - ro.taxes) / 100.

There are two tables being joined by an inner join.

Table 1) rpt_repair_order alias ro (transactional level table) from it we grab ro.shop_id, and a count(ro.repair_order_id) and sum(ro.total_sales - ro.taxes) / 100. To understand correctly you want to see the raw data that is output from that table based on the query?  Would a week work of data suffice for three stores just so that it's not a lot to insert into the dbfiddle test env?

Table 2) shop alias s (store information table) from it we grab s.nickname. Would the data for three stores suffice?
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

I just need test data to run your query against that shows the problem.  If you can show it with just three records, that is all I need.  If you need 100, then that is what I need.


I want to copy/paste the query you are running that shows the rank not working.  I'll then attempt to correct the SQL so it works as you want.