Select Top 3 And Then Group The Remainder

Colin Legg · #1 (**permalink**) 12-02-11, 13:51

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

Wim · #2 (**permalink**) 12-02-11, 14:25

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

Colin Legg · #3 (**permalink**) 12-04-11, 10:23

Thank you, Wim. I was able to tweak it to suit my needs.

Internet Services

Web Development

Digital Marketing

Consumer Tech