Select Failed: 3504: Selected non_aggregate values must be part of the associated group

Database

Select Failed: 3504: Selected non_aggregate values must be part of the associated group

I have 2 subselects in my query.  The 2nd subselect is giving me this problem.   I have tried multiple group by and I continue to get this error.  The SQL is below.  Thank you in advance for your help.

SELECT DISTINCT

ALL_MEMBER.MEDICAID_NBR,

ALL_MEMBER.SSN,

PHARMACY_FILL_DATE.DATE_DATE AS FILL_DATE,

TRUNC(MONTHS_BETWEEN(CURRENT_DATE,( birth_date.DATE_DATE ))/12) AS birth_date,

DIM_DRUG.GPI_NBR,

trunc(add_months(Current_Date,-12),'Q')AS FIRST_DAY_OF_LAST_QTR,

trunc(Current_Date,'Q')-1 AS LAST_DAY_OF_LAST_OF_QUARTER,

MEMBER_DRUG_COUNT.Member_Drug_Count

FROM

DIM_MEMBER ALL_MEMBER

INNER JOIN DIM_MEMBER_ELIGIBILITY MEMBER_ELIGIBILITY ON (MEMBER_ELIGIBILITY.EDW_MEMBER_CK=ALL_MEMBER.EDW_MEMBER_CK

AND MEMBER_ELIGIBILITY.PLAN_DIM_CK=ALL_MEMBER.PLAN_DIM_CK)

INNER JOIN FT_PHARMACY_CLAIM ON (MEMBER_ELIGIBILITY.MEMBER_ELIGIBILITY_CK=FT_PHARMACY_CLAIM.SUBSCRIBER_MEMBER_ELIG_DIM_CK)

INNER JOIN DIM_DRUG ON (FT_PHARMACY_CLAIM.DRUG_DIM_CK=DIM_DRUG.DRUG_DIM_CK)

INNER JOIN DIM_DATE PHARMACY_FILL_DATE ON (PHARMACY_FILL_DATE.DATE_DIM_CK=FT_PHARMACY_CLAIM.FILL_DATE_DIM_CK)

INNER JOIN dim_date birth_date ON all_member.birth_date_dim_ck = birth_date.date_dim_ck

INNER JOIN ( SELECT

COMPLIANCE_OWN.KY_PYSCH_DRUG.DOSAGE,

COMPLIANCE_OWN.KY_PYSCH_DRUG.DRUG_NAME,

COMPLIANCE_OWN.KY_PYSCH_DRUG.GPI,

COMPLIANCE_OWN.KY_PYSCH_DRUG.GCN,

COMPLIANCE_OWN.KY_PYSCH_DRUG.KTYCLASS,

COMPLIANCE_OWN.KY_PYSCH_DRUG.STRENGTH

FROM

COMPLIANCE_OWN.KY_PYSCH_DRUG

WHERE COMPLIANCE_OWN.KY_PYSCH_DRUG.KTYCLASS = 'ANTI-PSYCHOTICS: ATYPICALS' ) PYSCH_DRUG ON PYSCH_DRUG.GPI=DIM_DRUG.GPI_NBR

--************************************************************************

INNER JOIN (SELECT

ALL_MEMBER.MEDICAID_NBR AS Member_Nbr,

Count(DIM_DRUG.GPI_NBR||PHARMACY_FILL_DATE.DATE_DATE) AS Member_Drug_Count

FROM

DIM_MEMBER ALL_MEMBER

INNER JOIN DIM_MEMBER_ELIGIBILITY MEMBER_ELIGIBILITY ON (MEMBER_ELIGIBILITY.EDW_MEMBER_CK=ALL_MEMBER.EDW_MEMBER_CK

AND MEMBER_ELIGIBILITY.PLAN_DIM_CK=ALL_MEMBER.PLAN_DIM_CK)

INNER JOIN FT_PHARMACY_CLAIM ON (MEMBER_ELIGIBILITY.MEMBER_ELIGIBILITY_CK=FT_PHARMACY_CLAIM.SUBSCRIBER_MEMBER_ELIG_DIM_CK)

INNER JOIN DIM_DRUG ON (FT_PHARMACY_CLAIM.DRUG_DIM_CK=DIM_DRUG.DRUG_DIM_CK)

INNER JOIN DIM_DATE PHARMACY_FILL_DATE ON (PHARMACY_FILL_DATE.DATE_DIM_CK=FT_PHARMACY_CLAIM.FILL_DATE_DIM_CK)

INNER JOIN dim_date birth_date ON all_member.birth_date_dim_ck = birth_date.date_dim_ck

INNER JOIN ( SELECT

COMPLIANCE_OWN.KY_PYSCH_DRUG.DOSAGE,

COMPLIANCE_OWN.KY_PYSCH_DRUG.DRUG_NAME,

COMPLIANCE_OWN.KY_PYSCH_DRUG.GPI,

COMPLIANCE_OWN.KY_PYSCH_DRUG.GCN,

COMPLIANCE_OWN.KY_PYSCH_DRUG.KTYCLASS,

COMPLIANCE_OWN.KY_PYSCH_DRUG.STRENGTH

FROM

COMPLIANCE_OWN.KY_PYSCH_DRUG

WHERE COMPLIANCE_OWN.KY_PYSCH_DRUG.KTYCLASS = 'ANTI-PSYCHOTICS: ATYPICALS' ) PYSCH_DRUG ON PYSCH_DRUG.GPI=DIM_DRUG.GPI_NBR

WHERE

FT_PHARMACY_CLAIM.PLAN_DIM_CK IN (5,16)

AND

ALL_MEMBER.PLAN_DIM_CK IN (5,16)

AND

PHARMACY_FILL_DATE.DATE_DATE BETWEEN TRUNC(ADD_MONTHS(CURRENT_DATE,-12),'Q') AND trunc(Current_Date,'Q')-1

AND

REVERSAL_IND = 'N') MEMBER_DRUG_COUNT ON MEMBER_DRUG_COUNT.Member_Nbr=DIM_MEMBER.MEDICAID_NBR

--***********************************************************************************************************

WHERE

FT_PHARMACY_CLAIM.PLAN_DIM_CK IN (5,16)

AND

ALL_MEMBER.PLAN_DIM_CK IN (5,16)

AND

PHARMACY_FILL_DATE.DATE_DATE BETWEEN TRUNC(ADD_MONTHS(CURRENT_DATE,-12),'Q') AND trunc(Current_Date,'Q')-1

AND

REVERSAL_IND = 'N'

GROUP BY

1,2,3,4,5,6

2 REPLIES
Enthusiast

Re: Select Failed: 3504: Selected non_aggregate values must be part of the associated group

In fact you are using aggregate function in you query and forgot to use group by clause for non aggregate columns.

for example for the following part of your query the group by is missing:

-- ****************************************************** ******************

INNER JOIN (SELECT

ALL_MEMBER.MEDICAID_NBR AS Member_Nbr,

Count(DIM_DRUG.GPI_NBR||PHARMACY_FILL_DATE.DATE_DATE) AS Member_Drug_Count

FROM

DIM_MEMBER ALL_MEMBER

INNER JOIN DIM_MEMBER_ELIGIBILITY MEMBER_ELIGIBILITY ON (MEMBER_ELIGIBILITY.EDW_MEMBER_CK=ALL_MEMBER.EDW_MEMBE R_CK

AND MEMBER_ELIGIBILITY.PLAN_DIM_CK=ALL_MEMBER.PLAN_DIM_CK) 

INNER JOIN FT_PHARMACY_CLAIM ON (MEMBER_ELIGIBILITY.MEMBER_ELIGIBILITY_CK=FT_PHARMACY_ CLAIM.SUBSCRIBER_MEMBER_ELIG_DIM_CK)

INNER JOIN DIM_DRUG ON (FT_PHARMACY_CLAIM.DRUG_DIM_CK=DIM_DRUG.DRUG_DIM_CK)

INNER JOIN DIM_DATE PHARMACY_FILL_DATE ON (PHARMACY_FILL_DATE.DATE_DIM_CK=FT_PHARMACY_CLAIM.FILL _DATE_DIM_CK)

INNER JOIN dim_date birth_date ON all_member.birth_date_dim_ck = birth_date.date_dim_ck

INNER JOIN ( SELECT

COMPLIANCE_OWN.KY_PYSCH_DRUG.DOSAGE,

COMPLIANCE_OWN.KY_PYSCH_DRUG.DRUG_NAME,

COMPLIANCE_OWN.KY_PYSCH_DRUG.GPI,

COMPLIANCE_OWN.KY_PYSCH_DRUG.GCN,

COMPLIANCE_OWN.KY_PYSCH_DRUG.KTYCLASS,

COMPLIANCE_OWN.KY_PYSCH_DRUG.STRENGTH

FROM

COMPLIANCE_OWN.KY_PYSCH_DRUG

WHERE COMPLIANCE_OWN.KY_PYSCH_DRUG.KTYCLASS = 'ANTI-PSYCHOTICS: ATYPICALS' ) PYSCH_DRUG ON PYSCH_DRUG.GPI=DIM_DRUG.GPI_NBR

 GROUP BY 1 --This group by was missing

WHERE

FT_PHARMACY_CLAIM.PLAN_DIM_CK IN (5,16)

AND

ALL_MEMBER.PLAN_DIM_CK IN (5,16)

AND

PHARMACY_FILL_DATE.DATE_DATE BETWEEN TRUNC(ADD_MONTHS(CURRENT_DATE,-12),'Q') AND trunc(Current_Date,'Q')-1

AND

REVERSAL_IND = 'N') MEMBER_DRUG_COUNT

Secondly you have used group by at the end of query for 6 columns, but you are selecting 8 columns with out any aggregate function, Now you have to use group by 1,2,3,4,5,6,7,8 OR if it is not required then ommit it.  

Hope this will work for you!

Khurram
Enthusiast

Re: Select Failed: 3504: Selected non_aggregate values must be part of the associated group

You are not using any aggregate function in your SELECT clause & on top of that you are already doing DISTINCT alongwith GROUP BY clause (both of which has the same impact)

You can solve the error by either removing the GROUP BY clause altogether or mention all the columns in the GROUP BY caluse (and removing the DISTINCT in select clause)...