All Forums Database
YIAPIPI 9 posts Joined 02/14
12 Nov 2014
Problem in grouping multiple "case" statement

Hi everybody,
in my query i got : ” selected non aggregate value must be part of associated group” relating to the multiple case statement .
the problem isn't present if i run with the single line "--, SUM(AM_FNMN) AS TOT_AM_FNMN" .
could you address the matter?
thank's in advance for your precious support
Pio

select DISTINCT DT_CNTB ,CD_FNMN
, case
when ID_CLS_DATO='31' then 'AM_ACRDT_SS'
when ID_CLS_DATO='32' then 'ACRDT_OPRV_SS'
when ID_CLS_DATO='33' then 'AM_UTLZT_SS'
when ID_CLS_DATO='39' then 'AM_SCNFN_SS'
ELSE ID_CLS_DATO
END ID_CLS_DATO
--, SUM(AM_FNMN) AS TOT_AM_FNMN
, (case
when (ID_CLS_DATO='31') then SUM(AM_FNMN)
END) as TOT_AM_FNMN_ACRDT
, (case
when (ID_CLS_DATO='32') then SUM(AM_FNMN)
END) as TOT_AM_FNMN_ACRDT_OPRV
, (case
when (ID_CLS_DATO='33') then SUM(AM_FNMN)
END) as TOT_AM_FNMN_UTLZT
, (case
when (ID_CLS_DATO='39') then SUM(AM_FNMN)
END ) as TOT_AM_FNMN_SCNFN
FROM DXDWH_DB.DWH_SGNLZ_DATI_PER_CR
where DT_CNTB='2014-07-31'
AND (ID_ENT_GSTR='99' AND ID_ENT_SGNLN='1' AND ID_TP_FLS='10')
AND ID_CLS_DATO IN ('31','32','33','39')
GROUP BY 1,2,3

dnoeth 4628 posts Joined 11/04
12 Nov 2014

You need to fix your logic, e.g.

SUM(case
       when (ID_CLS_DATO='31') then AM_FNMN
    END) as TOT_AM_FNMN_ACRDT

 

Dieter

You must sign in to leave a comment.