Selected non-aggregate values must be part of the associated group - I am getting this error

Analytics
N/A

Selected non-aggregate values must be part of the associated group - I am getting this error

select
DEP.DEPARTMENT_NAME,
loc.location,
pe.EC,
b.STATUS_C

 

from (Select mg_id,
cx_time,
pat_X_id,
status_c,
count(*) as In_Count

FROM
IBx
where Cx_time >= date'2016-12-14'
and TYPE_C IN ( 7,8 )) as B

left join
(select
msg_id
from IB_REC) as ib
on b.mg_id=ib.mg_id


left join
(select PAT_x_ID,
ec

eid
from PAT_x ) as pe
ON ib.PAT_x_ID=A.PAT_x_ID

left join
(select
cid,
id as nyi
from mapiy ) as emp_v
on pe.EID=emp_v.cidi

 

left join
(Select
department_id,
department_name,
LOC_ID
from x_dep ) as dep
on dep.department_id=pe.department_id

 

left join
(select
loc_id,
loc_name as Location
from loc ) as loc
on dep.LOC_ID=loc.loc_id

group by
DEP.DEPARTMENT_NAME,
loc.location,
pe.EC,
b.STATUS_C


Accepted Solutions
Teradata Employee

Re: Selected non-aggregate values must be part of the associated group - I am getting this error

This query has a few other problems. For one thing there is no "A.PAT_x_ID" defined anywhere.  Also, ib.PAT_x_ID would not exist.  In fact, there are a lot of unnecessary derived tables.  This may not make it run slower, but it sure makes it hard to read.  I also think this is might be a product join, because there may be multiple values for cx_time, which is not in any predicate.  But try this:

 

select DEP.DEPARTMENT_NAME, loc.location, pe.EC, b.STATUS_C
 
from ( Select mg_id, cx_time, pat_X_id, status_c, count(*) as In_Count
FROM IBx
where Cx_time >= date'2016-12-14'
and TYPE_C IN ( 7,8 )
group by 1,2,3,4 ) as B

left join IB_REC ib
on B.mg_id=ib.mg_id

left join PAT_x pe
ON ib.PAT_x_ID=pe.PAT_x_ID

left join mapiy emp_v
on pe.EID=emp_v.cidi
 
left join x_dep dep
on dep.department_id=pe.department_id
 
left join loc
on dep.LOC_ID=loc.loc_id

group by DEP.DEPARTMENT_NAME, loc.location, pe.EC, B.STATUS_C

1 ACCEPTED SOLUTION
3 REPLIES
rjg
N/A

Re: Selected non-aggregate values must be part of the associated group - I am getting this error

Draed,

 

You need to have a group by in derived table B for the count(*).

Teradata Employee

Re: Selected non-aggregate values must be part of the associated group - I am getting this error

This query has a few other problems. For one thing there is no "A.PAT_x_ID" defined anywhere.  Also, ib.PAT_x_ID would not exist.  In fact, there are a lot of unnecessary derived tables.  This may not make it run slower, but it sure makes it hard to read.  I also think this is might be a product join, because there may be multiple values for cx_time, which is not in any predicate.  But try this:

 

select DEP.DEPARTMENT_NAME, loc.location, pe.EC, b.STATUS_C
 
from ( Select mg_id, cx_time, pat_X_id, status_c, count(*) as In_Count
FROM IBx
where Cx_time >= date'2016-12-14'
and TYPE_C IN ( 7,8 )
group by 1,2,3,4 ) as B

left join IB_REC ib
on B.mg_id=ib.mg_id

left join PAT_x pe
ON ib.PAT_x_ID=pe.PAT_x_ID

left join mapiy emp_v
on pe.EID=emp_v.cidi
 
left join x_dep dep
on dep.department_id=pe.department_id
 
left join loc
on dep.LOC_ID=loc.loc_id

group by DEP.DEPARTMENT_NAME, loc.location, pe.EC, B.STATUS_C

N/A

Re: Selected non-aggregate values must be part of the associated group - I am getting this error

Thank you so much!!!!!!!!!!!!!!! Your suggestion worked great!