导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

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

chinesealbert 2008-01-11 03:50:36
有表如下:
表名: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呢?
...全文
298 点赞 收藏 12
写回复
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
JL99000 2008-01-11
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
回复
yangjiexi 2008-01-11

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
回复
chinesealbert 2008-01-11
哇靠,能不能不要用变量啊???
回复
yangjiexi 2008-01-11

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 2008-01-11
以上的老大们,有没啥好点的函数做这个事情啊?

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

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

如在oracle上,就可以直接用RATIO_TO_REPORT解决问题,但换了sqlserver,就太难了
回复
wyb0026 2008-01-11
??刚才发过了了
回复
yanzimywife_2005 2008-01-11
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
回复
free1879 2008-01-11

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;
回复
wyb0026 2008-01-11
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
回复
wzy_love_sly 2008-01-11
缺1%,但都四舍不进去,我搞不了这问题,诶
回复
kk19840210 2008-01-11

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 2008-01-11
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%
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告