Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511

    Unanswered: Select Top 3 And Then Group The Remainder

    Hi,

    I have an aggregated table called T1 which looks like this:

    Code:
    Name     Amount
    a        10
    b        -4
    c        9
    d        5
    e        0
    f        0
    g        8
    h        3
    i        3
    I need to write a query which selects the top 3 names by amount which have an amount greater than 0.
    The remaining names which have amounts greater than 0 should be grouped together as 'others' at the bottom. Finally I need to add a total row. So the final result I am looking for is:

    Code:
    Name     Amount
    a         10
    c         9
    g         8
    others    11
    total     38
    The part I'm struggling with is how to collapse the remaining names into the 'others' group. Please would someone give me an outline of the best way to go about this?

    Thanks

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This should work:
    Code:
    DROP TABLE #DaTable
    CREATE TABLE #DaTable(
    	Name	CHAR(10)	NOT NULL,
    	Amount	INT		NOT NULL
    )
    
    INSERT INTO #DaTable(Name, Amount) VALUES
    ('a', 10), 
    ('b', -4), 
    ('c', 9), 
    ('d', 5), 
    ('e', 0), 
    ('f', 0), 
    ('g', 8), 
    ('h', 3), 
    ('i', 3)
    
    SELECT * from #DaTable
    
    GO
    WITH CTE AS
    (SELECT Name, Amount,
    	ROW_NUMBER() OVER (ORDER BY Amount DESC, Name Asc) AS RowNum
    FROM #DaTable
    WHERE Amount > 0
    )
    SELECT Name, Amount
    FROM CTE
    WHERE RowNum <= 3
    	UNION ALL
    SELECT 'Others', SUM(Amount)
    FROM CTE
    WHERE RowNum > 3
    	UNION ALL
    SELECT 'Total', SUM(Amount)
    FROM CTE
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Thank you, Wim. I was able to tweak it to suit my needs.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •