一个很简单的group by后汇总百分比的sql，请高手进来看看

[chinesealbert]

有表如下：
表名：table1
字段有两个：name amount
表的数据如下：
---------------
aa 10
bb 20
aa 10
bb 20
cc 30
---------------

select name, sum(amount)
from table1
group by name;

返回结果：
-------------
aa 20
bb 40
cc 30
------------

当前问题是，返回结果不仅要有有2列，还要求在加一列，这一列显示出name这个字段对应的总的汇总数量，占总数量的百分比。
如上边返回结果表明，表里的总数量是： 20+40+30=90

那么现在要求返回如下结果：
-------------
aa 20 23% (既20/90的结果)
bb 40 44% (既40/90的结果)
cc 30 33% (既30/90的结果)
------------

怎样写这个sql呢？

[wzy_love_sly]

declare @tb table (name varchar(10),amount int)
insert into @tb select 'aa',10
insert into @tb select 'bb',20
insert into @tb select 'aa',10
insert into @tb select 'bb',20
insert into @tb select 'cc',30

select   name,   sum(amount) as s
,ltrim(cast(round(sum(amount)*100.0/(select sum(amount) from @tb),0) as  int))+'%' as b
from   @tb
group   by   name

name s b
aa 20 22%
bb 40 44%
cc 30 33%

[kk19840210]

create table table1(name varchar(10),amount int)

insert into table1 values('aa',10)
insert into table1 values('bb',20)
insert into table1 values('aa',10)
insert into table1 values('bb',20)
insert into table1 values('cc',30)

select name,[sum(amount)]=sum(amount),[amount(%)]=convert(varchar(2),100*(select sum(amount) from table1 where name=a.name)/(select sum(amount) from table1))+'%' from table1 a group by name


name       sum(amount) amount(%)
---------- ----------- ---------
aa         20          22%
bb         40          44%
cc         30          33%

(3 行受影响)

[wzy_love_sly]

缺1%,但都四舍不进去，我搞不了这问题，诶

[wyb0026]

create table #T (a varchar(10),b int)
insert #T
select * from
(
select 'aa' a, 10 b union all
select 'bb', 20 union all
select 'aa', 10 union all
select 'bb', 20 union all
select 'cc', 30 ) T

select a,cast(cast(b*1.0/c *100 as int)as varchar(10))+'%' from
(select a,sum(b) b from #T
group by a) as t ,(select sum(b) c from #T) as t2
drop table #T

[free1879]

declare @sum float
select @sum = sum(amout) from table1
print @sum
select   name, sum(amout),convert(varchar(5),round(sum(amout)/@sum*100,2))+'%'
from   table1
group   by   name; 
 

[yanzimywife_2005]

create table table1(name varchar(10),amount int)

insert into table1 values('aa',10)
insert into table1 values('bb',20)
insert into table1 values('aa',10)
insert into table1 values('bb',20)
insert into table1 values('cc',30)

declare @allsum int
select @allsum=sum(amount) from table1 
print @allsum

select name,[sum(amount)]=sum(amount),[amount(%)]=substring(convert(varchar(15),0.5+100*(select sum(amount) from table1 where name=a.name)/(cast (@allsum as float))),0,3)+'%' from table1 a group by name

drop table table1

[wyb0026]

？？刚才发过了了

[chinesealbert]

以上的老大们，有没啥好点的函数做这个事情啊？

以上答案大多就会直接在table1上进行操作，但其实，table1是一个很大很复杂的select语句

所以有没啥更简单的方法？

如在oracle上，就可以直接用RATIO_TO_REPORT解决问题，但换了sqlserver，就太难了

[yangjiexi]

declare @table table(name varchar(10), amount int)

insert into @table
select 'aa',10
union all
select 'bb',20
union all
select 'aa',10
union all
select 'bb',20
union all
select 'cc',30

select name,sum(amount),
left(cast(round(sum(amount)*1.0/(select sum(amount) from @table),2)*100 as varchar(20)),2)+'%' 
from @table
group by name

[chinesealbert]

哇靠，能不能不要用变量啊？？？

[yangjiexi]

create table table1(name varchar(10), amount int)

insert into table1
select 'aa',10
union all
select 'bb',20
union all
select 'aa',10
union all
select 'bb',20
union all
select 'cc',30

select name,sum(amount),
left(cast(round(sum(amount)*1.0/(select sum(amount) from table1),2)*100 as varchar(20)),2)+'%' 
from table1
group by name

drop table table1

[JL99000]

if object_id('tb') is not null
drop table tb
go
create table tb(name varchar(10),amount int)
insert into tb select 'aa',10
insert into tb select 'bb',20
insert into tb select 'aa',10
insert into tb select 'bb',20
insert into tb select 'cc',30
select * from tb
select name,sum(amount),cast(convert(int,sum(amount)/cast((select sum(amount) from tb) as float)*100) as varchar(10))+'%'from tb
group by name