无法对包含聚合或子查询的表达式执行聚合函数。 [英] Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

问题描述

我的sql查询出现以下错误：





无法对包含聚合或子查询的表达式执行聚合函数。

消息130，等级15，状态1，行6



查询：

I get following error my sql query:


Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
Msg 130, Level 15, State 1, Line 6

Query:

SELECT COALESCE(pg_lego,0) as pg_lego, toal_anstnr as cc_grupp,CONVERT(NVARCHAR(255), Concat(per_namn_fore,' ',per_namn_efter)) as cc_ben, 
Sum( (CASE WHEN COALESCE((SELECT COALESCE(too_rappnr_efter,0) FROM torder_operation 

WHERE too_rappnr = toal_rappnr),0) = 0
THEN COALESCE(toal_antal_av ,0 ) ELSE 0 END)), 


Sum( COALESCE( toal_stall_av , 0 ) + COALESCE( toal_styck_av , 0 ) ) as cc_tid_av, 
Sum(	( COALESCE( toal_antal_av , 0 ) * ( ( COALESCE( toal_stall_pl , 0 ) + COALESCE( toal_styck_pl , 0 ) ) / 
CASE COALESCE( toal_antal_pl , 0 ) 
WHEN 0 THEN 1 ELSE toal_antal_pl END ) ) ) as cc_tid_pl, Sum(	(	COALESCE( toal_antal_av , 0 ) * ( ( COALESCE( toal_stall_pl , 0 ) + COALESCE( toal_styck_pl , 0 ) ) / 
CASE COALESCE( toal_antal_pl , 0 ) WHEN 0 THEN 1 ELSE toal_antal_pl END ) )	* (CASE WHEN pg_lego=1 THEN 1 ELSE COALESCE( ( pg_pfakt1+pg_pfakt2 ) , 1 ) END) ) as cc_kost_pl, 
Sum(	(	COALESCE(	toal_stall_av , 0 ) + COALESCE( toal_styck_av , 0 ) ) * (CASE WHEN pg_lego=1 THEN 1 ELSE COALESCE( ( pg_pfakt1+pg_pfakt2 ) , 1 ) END) ) as cc_kost_av FROM toa_logg 
LEFT OUTER JOIN rutin  	ON rutin.rut_id = toa_logg.rut_id 
LEFT OUTER JOIN rutin_beskrivning ON rutin_beskrivning.rut_id	=	rutin.rut_id AND rutin_beskrivning.rut_sprak_id	= '0' 
LEFT OUTER JOIN prodgrupp	ON prodgrupp.pg_pgrkod = COALESCE( toa_logg.toal_prg , toa_logg.toal_prg_pl ) 
LEFT OUTER JOIN avdelning	ON avdelning.avd_id = prodgrupp.avd_id 
LEFT OUTER JOIN leverantor	ON leverantor.l_id = toal_l_id 
LEFT OUTER JOIN artikel	ON art_artnr = toal_artnr 
LEFT OUTER JOIN artikel_plan_stalle	aspx ON aspx.art_id	= artikel.art_id AND aspx.ls_id	= COALESCE( 1 , art_akt_stalle , 1 ) 
LEFT OUTER JOIN artikel_plan_stalle	asp1 ON asp1.art_id	= artikel.art_id AND asp1.ls_id	= art_akt_stalle 
LEFT OUTER JOIN artikelkod ON artikelkod.ak_kod = artikel.ak_kod LEFT OUTER JOIN person ON person.per_anstnr = toal_anstnr 




WHERE 1=1 and COALESCE( toal_stalle_rapp , toal_stalle_order ) = 1 AND (cast(toal_datum as date) between '20190301' and '20190331') 
AND ( NOT ( (toal_artnr like 'AVD%')
AND toal_artnr IS NOT NULL )) AND
( NOT ( (COALESCE(artikel.ak_kod,'') between '640LMV' and '640LMV') AND COALESCE(artikel.ak_kod,'') IS NOT NULL ))
AND ((toal_anstnr between '1' and '4999') 
AND ( NOT ( (toal_anstnr between '3065' and '3065') 
AND toal_anstnr IS NOT NULL ) 
AND NOT ( (toal_anstnr between '1268' and '1268') 
AND toal_anstnr IS NOT NULL ))) AND COALESCE(pg_lego,0)=0 
GROUP BY pg_lego,toal_anstnr,person.per_namn_fore,person.per_namn_efter ORDER BY pg_lego,toal_anstnr,person.per_namn_fore,person.per_namn_efter ASC

我的尝试：



试图改变第6行：



WHERE too_rappnr = toal_rappnr），0）= 0



要：选择too_rappnr为...............

What I have tried:

Tried to change line 6:

WHERE too_rappnr = toal_rappnr),0) = 0

To: select too_rappnr as...............

推荐答案

请参阅此处： SQL GROUP由于列'名称'在选择列表中无效，因为......错误 [ ^ ]

这篇关于无法对包含聚合或子查询的表达式执行聚合函数。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！