arooaroo arooaroo - 2 months ago 9
SQL Question

SQL to produce Top 10 and Other

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

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.

Comments