Top 10 and rest result as Others except Top 10

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Top 10 and rest result as Others except Top 10

Author

Topic

parveen
Starting Member

4 Posts

Posted - 2009-10-23 : 05:31:41

Hi friends,
My sample data is as follows

DECLARE @Sample TABLE
(
fbNum INT,
Customer varchar(50),
paidAmount FLOAT,
Weight INT

)

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, 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

Here, I want a result like,Top 10 customers in grouping of a substring of the first 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

Any help is appreciated.
Thanks

Parveen.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-23 : 05:54:20

If you're able to code 2 select statements that fit your requirements then you can do that with UNION to have both results in one resultset.

No, you're never too old to Yak'n'Roll if you're too young to die.

parveen
Starting Member

4 Posts

Posted - 2009-10-23 : 06:55:49

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.

Parveen.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-23 : 07:26:01

[code]
create table #Sample
(
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

;with my_cte(Customer,paidAmount,[Weight])
as
(
select top 10
max(Customer) as Customer,
sum(paidAmount) as paidAmount,
sum([Weight]) as [Weight]
from #Sample
group by substring(Customer,1,5)
order by paidAmount desc
)
select * from my_cte union all
select
'Others' as Customer,
sum(paidAmount) as paidAmount,
sum([Weight]) as [Weight]
from #Sample
where substring(Customer,1,5) not in (select substring(Customer,1,5) from my_cte)

drop table #Sample
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

parveen
Starting Member

4 Posts

Posted - 2009-10-26 : 01:38:10

quote:

Originally posted by webfred


create table #Sample
(
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


;with my_cte(Customer,paidAmount,[Weight]) 
as
(
 select top 10
 max(Customer) as Customer,
 sum(paidAmount) as paidAmount,
 sum([Weight]) as [Weight]
 from #Sample
 group by substring(Customer,1,5)
 order by paidAmount desc
)
select * from my_cte union all
 select 
 'Others' as Customer,
 sum(paidAmount) as paidAmount,
 sum([Weight]) as [Weight]
 from #Sample
 where substring(Customer,1,5) not in (select substring(Customer,1,5) from my_cte)

drop table #Sample

No, you're never too old to Yak'n'Roll if you're too young to die.

Hi Webfred,
Thanks a ton ...It works fine !! Thank you :)

Parveen.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-26 : 02:28:10

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources