SELECT Failed 3504: Selected non-aggregate values must be part of the associated group

General
MMM
Enthusiast

SELECT Failed 3504: Selected non-aggregate values must be part of the associated group

I am not sure why an erorr message is occurring in my query. What am I doing wrong?

SELECT SUM(MEM_COUNT) MBR_MNTH,

P1_MBRMTH

FROM

(

SELECT

COUNT (MB.MB_JOIN_KEY) MEM_COUNT,

CASE WHEN MB.EFF_BEG_DT >= '2009/01/01' AND MB.EFF_END_DT <= '2009/01/31' THEN 'YES' ELSE 'NO' END AS P1_MBRMTH

FROM MB_TABLE MB

WHERE (MB.EFF_BEG_DT >= '2009/01/01' AND MB.EFF_END_DT <= '2009/01/31') OR

(MB.EFF_BEG_DT >= '2009/03/01' AND MB.EFF_END_DT <= '2009/04/30')

)MMM_MB

GROUP BY MEM_COUNT, P1_MBRMTH

;

2 REPLIES
Junior Contributor

Re: SELECT Failed 3504: Selected non-aggregate values must be part of the associated group

In the Derived Table there's a GROUP BY missing while the outer query groups by too many columns.

And it seems you don't need to nest at all, this should return the same result:

SELECT
COUNT (MB.MB_JOIN_KEY) MEM_COUNT,
CASE WHEN MB.EFF_BEG_DT >= '2009/01/01' AND MB.EFF_END_DT <= '2009/01/31' THEN 'YES' ELSE 'NO' END AS P1_MBRMTH
FROM MB_TABLE MB
WHERE (MB.EFF_BEG_DT >= '2009/01/01' AND MB.EFF_END_DT <= '2009/01/31') OR
(MB.EFF_BEG_DT >= '2009/03/01' AND MB.EFF_END_DT <= '2009/04/30')
)MMM_MB
GROUP BY P1_MBRMTH

Dieter

MMM
Enthusiast

Re: SELECT Failed 3504: Selected non-aggregate values must be part of the associated group

dnoeth-----Thank you for your clear explanation.