troubleshooting Question

Rank by Total Sales - MySQL

Avatar of Isaiah Melendez
Isaiah Melendez asked on
MySQL ServerSQL
20 Comments2 Solutions139 ViewsLast Modified:
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;
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 20 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 20 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros