hi hope this helps
solved = yes
why am i doing this ? = better t-SQL = using ROLLUP function =sql server 2005
drop create table insert data
-- Drop the table if it already exists
IF OBJECT_ID('dbo.SalesData', 'U') IS NOT NULL
DROP TABLE dbo.SalesData;
-- Create the table
CREATE TABLE dbo.SalesData (
Name CHAR(1) PRIMARY KEY,
Amount INT
);
-- Insert the provided data
INSERT INTO dbo.SalesData (Name, Amount)
VALUES
('A', 100),
('B', 200),
('C', 600),
('D', 800),
('E', 423),
('F', 400),
('G', 261),
('H', 368),
('I', 953),
('J', 324),
('K', 658),
('L', 267),
('M', 156),
('N', 398),
('O', 568),
('P', 568);
t-sql
;WITH Ranked AS ( SELECT Name,Amount,ROW_NUMBER() OVER (ORDER BY Amount DESC) AS rn FROM dbo.SalesData )
SELECT
ISNULL(GroupName, 'Sum') AS Name,SUM(Amount) AS Amount
FROM
( SELECT CASE WHEN r.rn > 10 THEN 'Others' ELSE r.Name END AS GroupName,r.Amount FROM Ranked r ) AS x
GROUP BY
GroupName WITH ROLLUP
ORDER BY
CASE WHEN GroupName = 'Others' THEN 1 WHEN GroupName IS NULL THEN 2 ELSE 0 END, SUM(Amount) DESC;
Result
