"group by a.xmjlb,a.xmmc"出错！！！

[yjdabc]

select xmjlb=(select xmjlb from base_jh_xmjlb where id=a.xmjlb),xmmc=(select xmmc from base_jh_jsxmmc where id=a.xmmc),round(sum(a.sl*a.dj),0) as 零利指标 from htqd_b1 a,base_jh_jsxmmc b where a.xmjlb=b.xmjlb_id and a.xmmc=b.id group by a.xmjlb,a.xmmc order by xmjlb
原来的SQL语句按xmjlb,xmmc汇总，但现在汇总“零利指标”需加入一个条件，在htqd_b1中有一个bfb字段，当bfb>0 and bfb<10时,零利指标=round(sum(a.sl*a.dj),0)*0.5,否则零利指标=round(sum(a.sl*a.dj),0)*0.8,我写的SQL是
select xmjlb=(select xmjlb from base_jh_xmjlb where id=a.xmjlb),xmmc=(select xmmc from base_jh_jsxmmc where id=a.xmmc),零利指标=(case when (a.bfb>0 and a.bfb<10) then round(sum(a.sl*a.dj),0)*0.5 else round(sum(a.sl*a.dj),0)*0.8 end) from htqd_b1 a,base_jh_jsxmmc b where a.xmjlb=b.xmjlb_id and a.xmmc=b.id group by a.xmjlb,a.xmmc order by xmjlb
运行出现错误:列 'a.bfb' 在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中,请问我该如何解决？？？

[victorycyz]

sum(case when (a.bfb>0 and a.bfb<10) then round(a.sl*a.dj),0)*0.5 else round(a.sl*a.dj),0)*0.8 end) as 零利指标

[shuiniu]

try:
select xmjlb=(select xmjlb from base_jh_xmjlb where id=a.xmjlb),
xmmc=(select xmmc from base_jh_jsxmmc where id=a.xmmc),
零利指标=round(sum (case when (a.bfb>0 and a.bfb<10) then a.sl * a.dj*0.5
else a.sl*a.dj*0.8 end),0)
from htqd_b1 a,base_jh_jsxmmc b
where a.xmjlb=b.xmjlb_id and a.xmmc=b.id group by a.xmjlb,a.xmmc order by xmjlb

[zjcxc]

楼上的应该可以了.