select failed 3504 selected non-aggregate values must be part of the associated group

Database
N/A

select failed 3504 selected non-aggregate values must be part of the associated group

I'm having trouble with the following SQL and am receiving the error message in the subject line.  I know it has something to do with adding a GROUP BY because of the second item in my SELECT clause, but I can't figure out where it goes:

Select Distinct
grp.GROUP_CUSTOMER_ID GrpNum
,max(b.Effective_Dt)
,grp.ExpDat
,c.PRODUCER_ID CURRENT_PRODUCER_ID
,c.CURRENT_PRODUCER_FULL_NM
,b.Agency_ID CURRENT_AGENCY_ID
,d.CURRENT_AGENCY_NM
,e.GROUP_COMMISSION_LEVEL CURRENT_GRP_COMMISSION_LVL
,e.INDIVIDUAL_COMMISSION_LEVEL CURRENT_INDV_COMMISSION_LVL
From
(
Select Distinct
a.GROUP_CUSTOMER_ID
,Max(EXPIRATION_DT) ExpDat
From
PSOR_Temp.PRODUCER_X_GROUP a
Group by
1
) Grp
Inner Join
(
Select Distinct
a.GROUP_CUSTOMER_ID
,a.PRODUCER_ID
,a.EXPIRATION_DT ExpDat
From
PSOR_Temp.PRODUCER_X_GROUP a
) Prod
on grp.GROUP_CUSTOMER_ID = Prod.GROUP_CUSTOMER_ID
and grp.ExpDat = prod.ExpDat
Inner join
PSOR_Temp.PRODUCER c
on Prod.PRODUCER_ID = c.PRODUCER_ID
Inner Join
PSOR_Temp.PRODUCER_X_AGENCY b
on Prod.PRODUCER_ID = b.PRODUCER_ID
and b.BUSINESS_TYPE Like '%group%'
and prod.ExpDat between b.EFFECTIVE_DT and b.EXPIRATION_DT
Inner Join
PSOR_Temp.AGENCY d
on b.AGENCY_ID = d.AGENCY_ID
Inner Join
PSOR_Temp.AGENCY_HISTORY e
on d.AGENCY_ID = e.AGENCY_ID --and e.EXPIRATION_DT> date
Inner Join
(
Select
Agency_ID
,max(EXPIRATION_DT) EXPIRATION_DT
From
PSOR_TEMP.AGENCY_HISTORY
Group by
1
) f
on e.AGENCY_ID = f.AGENCY_ID
and e.EXPIRATION_DT = f.EXPIRATION_DT

Any help would be greatly appreciated.  Thanks in advance--

1 REPLY
Junior Contributor

Re: select failed 3504 selected non-aggregate values must be part of the associated group

It depends on the expected result set.

You might have to put all other columns in your select list in a GROUP BY 1,3,4,5,6,7,8,9 or you might need a MAX(b.Effective_Dt) OVER (PARTITION BY whatever).

When i look at your query i see several possible improvements:

- There's a lot of DISTINCTs, i doubt they're actually neccessary. When the optimizer is not able to eliminate them during optimization (e.g. GROUP BY already resturns unique rows) it's a lot of overhead. 

- The MAX(date) selects to find the latest row can be replace with a simple RANK/ROW_NUMBER:

    Select Distinct
a.GROUP_CUSTOMER_ID
,Max(EXPIRATION_DT) ExpDat
From
PSOR_Temp.PRODUCER_X_GROUP a
Group by
1
) Grp
Inner Join
(
Select Distinct
a.GROUP_CUSTOMER_ID
,a.PRODUCER_ID
,a.EXPIRATION_DT ExpDat
From
PSOR_Temp.PRODUCER_X_GROUP a

with:

    Select Distinct -- probably not needed
a.GROUP_CUSTOMER_ID
,a.PRODUCER_ID
,a.EXPIRATION_DT ExpDat
From
PSOR_Temp.PRODUCER_X_GROUP a
QUALIFY RANK()
OVER (PARTITION BY GROUP_CUSTOMER_ID
ORDER BY ExpDat DESC) = 1

Dieter