My requirement is the following:
A table stores company names and sales amounts per week.
Company Week Sales
Co. 1 201237 $200
Co. 2 201237 $150
Co. 3 201237 $420
Co. 4 201237 $100
Co. 5 201237 $90
Co. 6 201237 $110
The desired result should look as follows:
Week Company Sales
201237 Co.3 $420
201237 Co.1 $200
201237 Co.2 $150
201237 Rest $300
In other words, the goal is to find and rank the top 3 companies in terms of total sales, and to group together the remaining companies (per week).
Rest = Co.4, Co. 5, Co. 6
Honestly, I have no clue how to achieve this.
Any help or comments are highly appreciated.
just translate your narration to SQL :-)
SELECT week, company, SUM(sales)
CASE WHEN RANK() OVER (PARTITION BY week ORDER BY sales DESC) <= 3 THEN company ELSE 'Rest' END AS company,
GROUP BY 1,2
Thanks Dieter for this excellent answer. Of course it works.
I'd give a lot for a universal translator from my thoughts to SQL :-)