CODEWITHSUNDEEP
sqlsql-server
Chris
Chris

Reputation: 28064

SQL Server 2005 - Select top N plus "Other"

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

Upvotes: 1

Views: 5048

Answers (5)

Tom H
Tom H

Reputation: 47402

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.

Upvotes: 1

Amy B
Amy B

Reputation: 110221

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.

Upvotes: 1

Tom
Tom

Reputation: 1381

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

Upvotes: 0

FallenAvatar
FallenAvatar

Reputation: 4079

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

Upvotes: 0

D&#39;Arcy Rittich
D&#39;Arcy Rittich

Reputation: 171579

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

Upvotes: 3

Related Questions