SQL to produce Top 10 and Other

Question

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've been searching for ages but can't seem to find any help which takes me beyond the standard top 10.

TIA

Answer 1

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
    Ctry AS 'Other',
    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.

Answer 2

SELECT Ctry, sum(Sales) Sales
FROM (SELECT COALESCE(T2.Ctry, 'OTHER') Ctry, T1.Sales
        FROM (SELECT Ctry, sum(Sales) Sales
                FROM Table1
              GROUP BY Ctry) T1
            LEFT JOIN 
             (SELECT TOP 10 Ctry, sum(sales) Sales
                FROM Table1
              GROUP BY Ctry) T2 
            on T1.Ctry = T2.Ctry
     ) T
GROUP BY Ctry