求助: 关于sum聚合函数的使用问题

[wscft]

我有一段代码如下:
当这样写的时候:
select round((h.inputqty + h.rtnqty + c.balqty * c.finrate / 100) *
(case when m.cst_grpno = 'SFC' then i.varover when m.cst_grpno <> 'SFC' then (select cm.varover from cst_mom10109 cm where m.cst_grpno = cm.grpno) end),4)
from dbo.cos20140 h holdlock,
dbo.cos20110 c holdlock,
dbo.mom10200 m holdlock,
dbo.inv10100 i
where h.year = 2005 and
h.month = 10 and
h.code = 'M' and
c.year = h.year and
c.month = h.month and
m.no = h.docno and
m.seq = h.seq and
c.mo = h.docno and
c.seq = h.seq and
i.part = h.part
--group by h.year, h.month
可以执行出来结果的, 输出为
无列名
1 20.0000000000000
2 8.0000000000000
3 6.0000000000000
4 10.0000000000000
可是当我这样写的时候
declare @l_wkqty numeric(14,4)

select @l_wkqty = sum（round((h.inputqty + h.rtnqty + c.balqty * c.finrate / 100) *
(case when m.cst_grpno = 'SFC' then i.varover when m.cst_grpno <> 'SFC' then (select cm.varover from cst_mom10109 cm where m.cst_grpno = cm.grpno) end),4)）
from dbo.cos20140 h holdlock,
dbo.cos20110 c holdlock,
dbo.mom10200 m holdlock,
dbo.inv10100 i
where h.year = 2005 and
h.month = 10 and
h.code = 'M' and
c.year = h.year and
c.month = h.month and
m.no = h.docno and
m.seq = h.seq and
c.mo = h.docno and
c.seq = h.seq and
i.part = h.part
--group by h.year, h.month
select str(@l_wkqty)
就提示执行出错,错误如下:
服务器: 消息 130，级别 15，状态 1，行 4
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
===========================================
而不是我所要的结果 汇总值 44.0000000000000
请问各位老大,第4行是case 语句, 提示的错误就是出现在case语句这个地方,
是不是sum聚合函数内部不能包含有case 选择语句呢,
请高手指教!

[子陌红尘]

declare @l_wkqty numeric(14,4)
select
@l_wkqty =sum(a.wkqty)
from
(select wkqty = round((h.inputqty + h.rtnqty + c.balqty * c.finrate / 100) *
(case when m.cst_grpno = 'SFC' then i.varover when m.cst_grpno <> 'SFC' then (select cm.varover from cst_mom10109 cm where m.cst_grpno = cm.grpno) end),4))
from dbo.cos20140 h holdlock,
dbo.cos20110 c holdlock,
dbo.mom10200 m holdlock,
dbo.inv10100 i
where h.year = 2005 and
h.month = 10 and
h.code = 'M' and
c.year = h.year and
c.month = h.month and
m.no = h.docno and
m.seq = h.seq and
c.mo = h.docno and
c.seq = h.seq and
i.part = h.part
--group by h.year, h.month
) a
select str(@l_wkqty)

[子陌红尘]

错误提示很显然：不能对包含子查询的列进行聚合函数处理。

多嵌套一级子查询，把复杂的处理扔到子查询中，然后在子查询外部实现聚合。

[iwl]

sum聚合函数内部可以包含有case 选择语句

只是不能对包含子查询的列进行聚合函数处理

[wscft]

好的, 多谢楼上的两位帮忙了

[Well]

用sum進行匯總
TO：libin_ftsafe(子陌红尘)說的對。
對其函數sum的具體的用法可以查看聯機幫助。