sqlgroup-by

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.


Solution

  • 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.