SQL Server 2005 - Select top N plus "Other"

Question

I have a table for which I want to select top the 5 rows by some column A. I also want to have a 6th row titled 'Other' which sums the values in column A for all but the top 5 rows.

Is there an easy way to do this? I'm starting with:

select top 5 
    columnB, columnA 
from 
    someTable t
order by
    columnA desc

Answer 1

Not tested, but try something like this:

select * from (
    select top 5 
        columnB, columnA 
    from 
        someTable t
    order by
        columnA desc
    union all
    select 
     null, sum(columnA) 
    from 
        someTable t
    where primaryKey not in   (
     select top 5 
         primaryKey
     from 
         someTable t
     order by
         columnA desc
    )  
) a

Answer 2

This is off the top of my head, and i will garuntee horribly efficient:

SELECT TOP 5 columnB, columnA
FROM comTable t
ORDER BY columnA desc

UNION

SELECT 'Other', (A.Sum - B.Sum) AS Summary
FROM (SELECT SUM(columnA) FROM someTable) A
JOIN (SELECT SUM(One) FROM 
    (SELECT TOP 5 columnA FROM someTable ORDER BY columnA DESC)) B

Answer 3

I have recently used the EXCEPT statemnet a lot: (Not tested but I give it a go)

select top 5 
    columnB, columnA 
from 
    someTable t
order by
    columnA desc
UNION ALL
SELECT 'OTHER' ColumnB, SUM(ColumnA)
FROM
(SELECT ColumnB, ColumnA 
FROM someTable t
EXCEPT
select top 5 
    columnB, columnA 
from 
    someTable t
order by
    columnA desc
) others

Answer 4

select top 5 columnB, columnA
from someTable 
order by columnA desc

select SUM(columnA) as Total
from someTable

Do the subtraction on the client side.

Answer 5

100% untested, and off the top of my head, but you can give something like this a go. If I have a chance to test tonight I'll update the post, but there's a bottle of wine open for dinner and it's Friday night... :)

WITH CTE AS
     (
     SELECT
          ColumnB,
          ColumnA,
          ROW_NUMBER() OVER (ORDER BY ColumnB) AS RowNumber
     FROM
          dbo.SomeTable
     )
 SELECT
      CASE WHEN RowNumber <= 5 THEN ColumnB ELSE 'Other' END AS ColumnB,
      SUM(ColumnA) AS ColumnA
 FROM
      CTE
 GROUP BY
      CASE WHEN RowNumber <= 5 THEN ColumnB ELSE 'Other' END
 ORDER BY
      MIN(RowNumber)

EDIT: Looks like this worked after a couple of silly syntax errors. I've corrected those, so it should work as listed above now. I can't speak to performance on a large data set though, but it's worth giving it a shot.