Sql 分组统计 并就算所占的比例
0
悬赏园豆：5
[待解决问题]
需求：计算Expendtype分组出来后 所占全部数量的比例
Num/总数量
表结构如图
所有回答(3)
0
SELECT ExpendType,SUM(Number),MIN(Total),SUM(Number)*1.0/MIN(Total)
FROM (SELECT ExpendType,Number, Total=(SELECT SUM(Number) FROM dbo.TExpend) FROM dbo.TExpend )f
GROUP BY ExpendType
0
SELECT ExpendType,SUM(Number),MIN(Total),SUM(Number)*1.0/MIN(Total)
FROM (SELECT ExpendType,Number, Total=(SELECT SUM(Number) FROM dbo.TExpend) FROM dbo.TExpend )f
GROUP BY ExpendType
1楼这个SQL对不，感觉上好像有问题
我的水平有限啊 只想到这种方法 我测试了 好像没啥问题
你哪有更好的方法吗
0
SQL SERVER下可以这样写. 如果是其它数据库,也可以用子查询来实现
;with ExpSUM
as(
select
ExpendType,
SUM(Number) as ExpendTypeSUM
from TExpend
group by ExpendType
)
select
ExpendType,
ExpendTypeSUM,
(1.0*ExpendTypeSUM)/SUM(ExpendTypeSUM) OVER() as ExpendTypeRatio
from ExpSUM
