Rank by Total Sales - MySQL
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;
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
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
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
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
>> 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.
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.
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;
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 |
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
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.
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?
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial