Find Top N Elements and Group The Rest

Database

Find Top N Elements and Group The Rest

Dear All,

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.

Best Regards,

Christoph

3 REPLIES
N/A

Re: Find Top N Elements and Group The Rest

Hi Christoph,

just translate your narration to SQL :-)

SELECT week, company, SUM(sales)
FROM
(
SELECT
CASE WHEN RANK() OVER (PARTITION BY week ORDER BY sales DESC) <= 3 THEN company ELSE 'Rest' END AS company,
week, sales
FROM tab
) dt
GROUP BY 1,2

Dieter

Re: Find Top N Elements and Group The Rest

Thanks Dieter for this excellent answer. Of course it works.

I'd give a lot for a universal translator from my thoughts to SQL :-)

Christoph

Re: Find Top N Elements and Group The Rest

thanq Mr.doneth