Link to home
Start Free TrialLog in
Avatar of Isaiah Melendez
Isaiah Melendez

asked on

Rank by Total Sales - MySQL

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

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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

Avatar of Isaiah Melendez

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial

>>  like shown in this example


And in the SQL I already posted...

@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?
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
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.
These gentlemen were outstanding.

>> 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.

How would you like me to present the data for you? csv?

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.

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

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
;

Open in new window


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


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
is not lining up*

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.



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?

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.