Select Top(5) and all others into 'Other'

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select Top(5) and all others into 'Other'

Author

Topic

tig2810
Starting Member

9 Posts

Posted - 2009-09-28 : 12:12:28

Hi
I have a simple query that returns the sum of quantities grouped by vendor as below:

Quantity Vendor Name
76100 Hong Kong Vendor
61600 Book Vendor
12900 Midwest Vendor
11270 New Delhi Vendor
10390 West Coast Vendor
8050 Hong Kong Vendor
7415 Outback Vendor
7215 East Coast Vendor
7150 Vendor
6850 Australian P Vendor
6150 India Vendor
5900 Kangaroo Vendor
5875 Bombay Vendor

What I would like to do is return the Top 5 and all other values in 'other' as below. I have seen this technique a few times in charts but cannot find how to implement it in SQL?

Quantity Vendor Name
76100 Hong Kong Vendor
61600 Book Vendor
12900 Midwest Vendor
11270 New Delhi Vendor
10390 West Coast Vendor
54605 Other

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-28 : 12:52:31

Do you know before hand which vendors are the top 5? If so, you can select and group by a CASE expression.

Be One with the Optimizer
TG

tig2810
Starting Member

9 Posts

Posted - 2009-09-28 : 12:55:44

No sorry, it will potentially always be different as the data is constantly changing.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-28 : 12:56:59

Then maybe this:


select coalesce(d.[Vendor Name], 'Other') as [Vendor Name]
       ,sum(t.Quantity)                   as TotalQuantity
from   [yourTable] t
left outer join
       (
       select top 5 [Vendor Name] 
       from [yourTable] 
       group by [Vendor Name] 
       order by sum(Quantity) desc
       ) d
              on d.[Vendor Name] = t.[vendor name]
group by  case when d.[vendor name] is null then 1 else 0 end 
       ,coalesce(d.[Vendor Name], 'Other') 
order by case when d.[vendor name] is null then 1 else 0 end
       ,sum(t.Quantity) desc

EDIT:
added the sort to match your example
Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources