Question

oracle 利用 分组求和一张报表的 字段 金额 sql

一张报表 oracle 将 部门合计 （元）进行求和 作为 金额（元）的值 sql 写出来 金额 没 进行求和

select decode(t.f_disqua_level,'1','A类','2','B类','3','C类','4','D类','') as 等级,
t.f_process as 发现地点 ,
t.f_discovery_time as 发现时间,
t.f_description as 不合格描述,
b.f_dutydept as 责任部门 ,
b.f_assess_multiple as 考核倍数,
sum(b.f_assess_sum) OVER(PARTITION BY b.f_assess_sum) as 金额（元）
b.f_assess_sum as 部门合计
from t_quality_dis_process t,t_quality_dis_dutydept b-
where t.f_status=4
and b.f_disqua_id=t.f_id
and to_char(t.f_discovery_time,'yyyymm') between '201503' and '201503'
group by b.f_assess_sum,t.f_discovery_time,t.f_process ,t.f_disqua_level, t.f_description , b.f_dutydept, b.f_assess_multiple ,t.f_status
order by t.f_discovery_time





Answer 1

select decode(t.f_disqua_level,'1','A类','2','B类','3','C类','4','D类','') as 等级, t.f_process as 发现地点, t.f_discovery_time as 发现时间, t.f_description as 不合格描述, b.f_dutydept as 责任部门, b.f_assess_multiple as 考核倍数, b.f_assess_sum as 金额（元）, c.sum_count as 部门合计
from t_quality_dis_process t,t_quality_dis_dutydept b,
(select a.f_dutydept,
sum(a.f_assess_sum) OVER(PARTITION BY a.f_assess_sum) sum_count
from t_quality_dis_dutydept a
group by a.f_dutydept) c
where t.f_status=4
and b.f_disqua_id=t.f_id
and to_char(t.f_discovery_time,'yyyymm') between '201503' and '201503'
and c.f_dutydept = b.f_dutydept
order by t.f_discovery_time