Top 10 rows and rest as Others

Hi friends,
My sample data is as follows


DECLARE @Sample TABLE
(
fbNumINT,
Customervarchar(50),
paidAmountFLOAT,
WeightINT

)

INSERT INTO @Sample
SELECT 39161976,'AMC-CARTHAGE',50.00,2 UNION
SELECT 39161988,'AMC-CARTHAGE',60.00,3 UNION
SELECT 39161985,'AMC-CARTHAGE',50.00,4 UNION
SELECT 38451329,'CITRY HEALTHARE',11646.00,39915 UNION
SELECT 39195692,'WESCO AIRCRAFT',9668.00,1 UNION
SELECT 39195691,'WESCO AIRCRAFT',9187.00,1 UNION
SELECT 39181135,'FREUDENBERG NOL',7948.00,20435 UNION
SELECT 39181136,'HOME', 20164.03,64867UNION
SELECT 39195700,'BARDONS AND OLI',4204.34,20000 UNION
SELECT 38714013,'VALASSIS',4196.33,3270 UNION
SELECT 39198077,'PAGELL',3178.19,17134 UNION
SELECT 39205936,'DELPHI',2000.00,50 UNION
SELECT 39205942,'DELPHI',3000.00,50 UNION
SELECT 39199519,'UNITED SOLAR SY',2550.00,3660

Here,I want a result like,Top 10 customers in grouping of a substring of thefirst five characters of the customer name and rest all as 'Others'with sum of weight and paid amount other than first 10.

So Expected output is something like this ..

'AMC-CARTHAGE',110.00,9
'CITRY HEALTHARE',11646.00,39915
'WESCO AIRCRAFT',18855.00,2
'FREUDENBERG NOL',7948.00,20435
'HOME', 20164.03,64867
'BARDONS AND OLI',4204.34,20000
'VALASSIS',4196.33,3270
'PAGELL',3178.19,17134
'DELPHI',5000.00,100
'UNITED SOLAR SY',2550.00,3660
'Others',1215487454.00,551515151

Thanks.

Hi Frank thanks for your reply,
I shall be more clear with data and requirement.
sample data as follows - ( I have added few rows to previous data thats all )
DECLARE @Sample TABLE
(
fbNum INT,
Customer varchar(50),
paidAmount FLOAT,
Weight INT

)

INSERT INTO @Sample
SELECT 39161976, 'AMC-CARTHAGE', 5000.00, 2 UNION
SELECT 39161988, 'AMC-CARTHAGE', 6000.00, 3 UNION
SELECT 39161985, 'AMC-CARTHAGE', 5000.00, 4 UNION
SELECT 38451329, 'CITRY HEALTHARE', 11646.00, 39915 UNION
SELECT 39195692, 'WESCO AIRCRAFT', 9668.00, 1 UNION
SELECT 39195691, 'WESCO AIRCRAFT', 9187.00, 1 UNION
SELECT 39181135, 'FREUDENBERG NOL', 7948.00, 20435 UNION
SELECT 39181136, 'HOME', 20164.03, 64867 UNION
SELECT 39195700, 'BARDONS AND OLI', 4204.34 ,20000 UNION
SELECT 38714013, 'VALASSIS', 4196.33, 3270 UNION
SELECT 39198077, 'PAGELL', 3178.19, 17134 UNION
SELECT 39205936, 'DELPHI', 2000.00, 50 UNION
SELECT 39205942, 'DELPHI', 3000.00, 50 UNION
SELECT 39199519, 'UNITED SOLAR SY', 2550.00, 3660 UNION
SELECT 39036260, 'BIG D SUPPLIES',3778.93, 38714 UNION
SELECT 39158839, 'FAURECIA INTERI', 3700.00, 1 UNION
SELECT 38849481, 'THYSSENKRUPP AE',3630.19, 5500
Here i need an output with grouping of substring of first Five characters ( i.e say delphi has 2 rows so that need to be counted as single entry n summed up) and ordering is by sum of paid amount in big to small order.And Others is sum of rest customers.
So output needed is like -
'HOME',20164.03 64867
'WESCO AIRCRAFT',18855, 2
'AMC-CARTHAGE',16000, 9
'CITRY HEALTHARE',11646 ,39915
'FREUDENBERG NOL',7948, 20435
'DELPHI',5000, 100
'BARDONS AND OLI', 4204.34, 20000
'VALASSIS', 4196.33, 3270
'BIG D SUPPLIES', 3778.93, 38714
'FAURECIA INTERI', 3700, 1
'Others' , 9358.38,26294
Thanks & regards
Parveen.

Yes Frank Thanks a lot, That works fine but just need to add that substring part.cause there is inconsistency in data,so i need to add up all that first 5 characters match for customer.
Thanks a ton !!
Regards
Parveen.

Hi,
I think that can be done with this ,what say ?
;WITH cte
AS(SELECT
ROW_NUMBER() OVER (ORDER BY SUM(paidAmount) DESC) AS rn, MAX(S.Customer) AS Customer,
SUM(S.paidAmount) AS PaidAmount,
SUM(S.Weight) AS Weight
FROM
@Sample S
GROUP BY
substring( S.Customer,1,5))

SELECT
cte.Customer,
cte.PaidAmount,
cte.Weight
FROM
cte
WHERE
rn < = 10
UNION ALL
SELECT
'others',
SUM(cte.PaidAmount),
SUM(cte.Weight)
FROM
cte
WHERE
rn > 10
Thanks
Parveen

Yeah i agree,but the reason is its our maintainence and enhancement project and database size is 18 gb.
Once again thanks.
Regards
Parveen.