Imagine I have a table showing the sales of Acme Widgets, and where they were sold. It's fairly easy to produce a report grouping sales by country. It's fairly easy to find the top 10. But what I'd like is to show the top 10, and then have a final row saying Other. E.g.,
Ctry | Sales
GB | 100
US | 80
ES | 60
IT | 10
Other | 50
I tried some of the other solutions here, however they seem to be either slightly off, or the ordering wasn't quite right.
My attempt at a Microsoft SQL Server solution appears to work correctly:
SELECT Ctry, Sales FROM ( SELECT TOP 2 Ctry, SUM(Sales) AS Sales FROM Table1 GROUP BY Ctry ORDER BY Sales DESC ) AS Q1 UNION ALL SELECT 'Other' AS Ctry, SUM(Sales) AS Sales FROM Table1 WHERE Ctry NOT IN (SELECT TOP 2 Ctry FROM Table1 GROUP BY Ctry ORDER BY SUM(Sales) DESC)
Note that in my example, I'm only using TOP 2 rather than TOP 10. This is simply due to my test data being rather more limited. You can easily substitute the 2 for a 10 in your own data.
Here's the SQL Script to create the table:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Table1]( [Ctry] [varchar](50) NOT NULL, [Sales] [float] NOT NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF
And my data looks like this:
GB 10 GB 21.2 GB 34 GB 16.75 US 10 US 11 US 56.43 FR 18.54 FR 98.58 WE 44.33 WE 11.54 WE 89.21 KR 10 PO 10 DE 10
Note that the query result is correctly ordered by the Sales value aggregate and not the alphabetic country code, and that the "Other" category is always last, even if it's Sales value aggregate would ordinarily push it to the top of the list.
I'm not saying this is the best (read: most optimal) solution, however, for the dataset that I provided it seems to work pretty well.