1. SQL Recipes
2. Calculations Per Group
3. Calculating Top N items and Aggregating (sum) the remainder into "All other"

Calculating Top N items and Aggregating (sum) the remainder into "All Other"

The top / bottom N problem is often seen when ranking items in your database. We've previously written about the ranking problem in Calculating Top N items per Group.

Another requirement that co-exists with this question is to rollup all the other rows into a row called "All other". The reason for doing this is to provide more context when presenting data.

Example data

Consider a table with two columns: a salesperson's name and the amount of business they've closed:

1. Calculating simple top N items

Our first question is to list the top 10 salespeople by business closed, which can be accomplished with the following query. For more sophistication with window functions, see Calculating Top N items per Group:

select name,
    sales
from deals
order by 3 desc
limit 10

2. Aggregating into "All Other"

If we were to visualize the top salespeople as a pie chart, it'd look something like this:

The problem with the above chart is that we are missing data on how much business the other salespeople closed to put the visualization in context. We'll attempt to solve this question now.

What we are trying to accomplish is a final row appended to the results above that is the aggregation of the rest of the data, something like this:

The following query accomplishes that:

with top10 as
    (select name,
            sales
    from deals
    order by 2 desc
    limit 10)
select *
from top10
union all
select 'All other' as name,
    sum(sales) as sales
from sales
where name not in
        (select name
        from top10)

How it works

We've organized the query into two queries and combined them with the union keyword. The first query is as before that finds the top 10 salespeople. The second builds upon this by excluding names that were previously found with the not in keyword. We've additionally "refactored" the core query as a CTE using the with keyword.