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

MySQL ServerSQL

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon
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

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
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
slightwv (䄆 Netminder)

>>  like shown in this example


And in the SQL I already posted...

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Isaiah Melendez

ASKER
@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 Johannsson

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
Isaiah Melendez

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Isaiah Melendez

ASKER
These gentlemen were outstanding.
slightwv (䄆 Netminder)

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

Isaiah Melendez

ASKER
How would you like me to present the data for you? csv?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
slightwv (䄆 Netminder)

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.

Isaiah Melendez

ASKER
Isaiah Melendez

ASKER
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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
slightwv (䄆 Netminder)

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


Isaiah Melendez

ASKER
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
Isaiah Melendez

ASKER
is not lining up*
Your help has saved me hundreds of hours of internet surfing.
fblack61
slightwv (䄆 Netminder)

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.



Isaiah Melendez

ASKER
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?
slightwv (䄆 Netminder)

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.

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.