create table AccTable
(account char(5), value int)
GO
insert AccTable values ('acc1', 100)
insert AccTable values ('acc2', 400)
insert AccTable values ('acc3', 200)
insert AccTable values ('acc4', 800)
insert AccTable values ('acc5', 300)
insert AccTable values ('acc6', 800)
insert AccTable values ('acc7', 400)
insert AccTable values ('acc8', 900)
insert AccTable values ('acc9', 50)
insert AccTable values ('acc10', 200)
GO

CREATE VIEW Agg1
AS
SELECT TOP 5 t1.account, t1.value
FROM AccTable t1
ORDER BY t1.value DESC
GO
CREATE VIEW Agg2
AS
SELECT 'other' account, sum(t2.value) value
FROM AccTable t2
WHERE t2.account NOT IN (SELECT TOP 5 t3.account
FROM AccTable t3
ORDER BY t3.value DESC)
GO
CREATE VIEW AccAgg
AS
select * from Agg1
union all
select * from Agg2
GO

SELECT * FROM AccAgg

select
	c.Account,
	c.Value
from
	(
	select a.*
	from
		(
		select top 5
			t1.account,
			t1.value,
			Type = 1
		from
			acctable t1
		order by
			t1.value desc
		) a
	union all
	select b.*
		from
		(
		select 
			'other' account,
			sum(t2.value) value,
			Type = 2
		from 
			acctable t2
		where
			t2.account not in
			(
			select top 5
				t3.account
			from
				acctable t3
			order by
				t3.value desc
			)
		) b
	) c
order by
	c.Type,
	c.Value desc,
	c.Account

Account Value       
------- ----------- 
acc8    900
acc4    800
acc6    800
acc2    400
acc7    400
other   850

(6 row(s) affected)

select 
	Account =
	case when b.account is null then 'Other' else a.account end,
	Value = sum(a.value)
from 
	acctable a
	left join
	(
	select top 5
		t3.account, t3.value
	from
		acctable t3
	order by
		t3.value desc
	) b
	on a.Account = b.Account
group by
	case when b.account is null then 2 else 1 end,
	case when b.account is null then 'Other' else a.account end
order by
	case when b.account is null then 2 else 1 end,
	sum(a.value) desc,
	case when b.account is null then 'Other' else a.account end

Account Value       
------- ----------- 
acc8    900
acc4    800
acc6    800
acc2    400
acc7    400
Other   850

(6 row(s) affected)

select	account = case when rank > 5 then 'Other' else account end
	,sum(value) value
from	(
	select	account
		,rank = (select count(*) from Acctable where value >= a.value)
		,value
	from	AccTable a
	) a
group by case when rank > 5 then 'other' else account end
	,case when rank > 5 then 2 else 1 end
order by case when rank > 5 then 2 else 1 end
	,value desc

quote:

---

Originally posted by duhaas

I have the following now:

select company = case when b.company is null then 'Other' else a.company end, value = sum(a.accountmv)

from

snapsraw a left join ( select top 5 c.company, c.accountmv from snapsraw c order by c.accountmv desc) b

on a.company = b.company

where branchstate = 'Arizona' and monthend = '08/01/2006'

group by case when b.company is null then 2 else 1 end,

case when b.company is null then 'Other' else a.company end

order by case when b.company is null then 2 else 1 end,

sum(a.accountmv) desc, case when b.company is null then 'Other' else a.company end

The results I get are only the other company market value and doesnt include the top 5?

---