Interesting. I don't get the same percentages as you:

create table #t
(
CustomerID nchar (5) primary key
, Orders int not null
)

insert #t
select top 10
CustomerID
, count (*)
from
Orders
group by
CustomerID
order by
count (*) desc

select
t1.*
, 100.0 * t1.Orders / (select sum (Orders) * 1.0 from #t) as Percentage
from
#t t1
go

drop table #t
go


--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
[quoted text, click to view]
Tom,

Here's a way to avoid recreating the result set that uses ROLLUP to
get the total into the result set, and a sneaky join with the table of
all brands to get the brands with the total in the same row. Here's an
example from Northwind - it gets the top 10 customers by number of
orders descending (with ties, since there is a tie for 10th), and gives
the percentages each customer's orders account for from among those top
10 (actually 12) customers listed.

I would be hard pressed to say this is better, since with only 10
rows, the temp table solution is hardly a problem, but I'm posting it
wondering if anyone can make it a bit simpler. Somehow I don't think it
needs three levels of aggregation.

select
coalesce(T.CustomerID,C.CustomerID) as CustomerID,
min(T.Orders) as Orders,
cast(min(T.Orders)*100.00/max(T.Orders) as decimal(5,2)) as [%]
from (
select
CustomerID,
sum(Orders) as Orders
from (
select top 10 with ties
CustomerID,
count(OrderID) as Orders
from Northwind..Orders
group by CustomerID
order by count(OrderID) desc
) T
group by CustomerID, Orders
with rollup
having grouping(CustomerID) = grouping(Orders)
) T left outer join (
select CustomerID
from Northwind..Customers
) C
on coalesce(T.CustomerID,C.CustomerID) = C.CustomerID
group by coalesce(T.CustomerID,C.CustomerID)
having count(*) = 2
go

Steve Kass
Drew University

[quoted text, click to view]