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

Union the top ten with an outer Join of the top ten with the table it self to aggregate the rest.

I don't have access to SQL here but I'll hazzard a guess:

select top (10) Ctry, sales from table1
union all 
select 'other', sum(sales)
from table1 
left outer join (select top (10) Ctry, sales from table1) as table2
on table2.Ctry = table2.Ctry 
where table2.ctry = null
group by table1.Ctry

Of course if this is a rapidly changing top(10) then you either lock or maintain a copy of the top(10) for the duration of the query.

Answer 2

In pseudo SQL: select top 10 order by sales UNION select 'Other',SUM(sales) where Ctry not in (select top 10 like above)

Answer 3

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

Answer 4

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.