% of total count as column

[Ninjamynci]

Hi all,
I need to create a computed column that counts values within a group and divides this by the total count for all groups to give a percentage of the total count.

e.g.

Sample data
Clientid Sales
------- -----
1 3
1 1
2 2
2 3
2 1
3 2
3 1
4 5
4 2

The output I need is:
Client_id Total Count ClientCount %of total
-------- ----------- ----------- ---------
1 20 4 20
2 20 6 30
3 20 3 15
4 20 7 35

I'm using SQL Server 2005. Can anyone help?

Cheers

[steenbt]

there are always more than one solution but since you are using SQL 2005 you could use the new over() clause like:

select distinct clientid, sum(sales) over() totalSales,
sum(sales) over(partition by clientid) clientTotalSales,
(sum(sales) over(partition by clientid) / sum(sales) over() ) * 100.0 percentOfTotal
from salesData

or use CTEs

with totalSales(total)as
(
select sum(sales) from salesData
)
select clientid, total, sum(sales), sum(sales) / total * 100.0
from salesData
cross join totalsales
group by clientid, total