Failure 3504:Selected non-aggregate values must be part of associated group

Database
Highlighted
Enthusiast

Failure 3504:Selected non-aggregate values must be part of associated group

Hi,

 

I need to select all columns(perform SUM on few cols) and group by selected fields to fetch distinct values so i am using below SQL which throws 3504 error please help me with the resolution

 

Throws Error:

SELECT col1,col2,col3,col4,col5,SUM(col6),SUM(col7) from table A
QUALIFY count(*) OVER (PARTITION BY col1,col2,col5 order by col4 desc)=1;

 

Working fine without aggregrate function:

 

select * from table  A QUALIFY count(*) OVER (PARTITION BY col1,col2,col5 order by col4 desc)=1;

 

Thanks,

Pavan

 

 

 

 

4 REPLIES 4
Teradata Employee

Re: Failure 3504:Selected non-aggregate values must be part of associated group

A non-windowed SUM() requires a GROUP BY phrase, and it must include all the columns selected but not involved in the aggregation, so:

 

SELECT col1,col2,col3,col4,col5,SUM(col6),SUM(col7) from table A
Group By col1,col2,col3,col4,col5
QUALIFY count(*) OVER (PARTITION BY col1,col2,col5 order by col4 desc)=1;

 

would be syntactially correct. Is that what you're looking for?

Enthusiast

Re: Failure 3504:Selected non-aggregate values must be part of associated group

Let me explain we have table with 7 cols so i need to perform SUM of selected ones and fetch other cols as well by performing group by on key cols and load distinct records to the table so query should be

 

SELECT col1,col2,col3,col4,col5,SUM(col6),SUM(col7) from table A
Group By col1,col2,col5
QUALIFY count(*) OVER (PARTITION BY col1,col2,col5 order by col4 desc)=1;

Enthusiast

Re: Failure 3504:Selected non-aggregate values must be part of associated group

Hi Coleman,

 

Please ignore previous post my requirement is to fetch max(datetime) value if we have duplicate rows so i am trying to fetch duplicate records and get latest datetime value(43:58.0).My SQL throws error please help me with the resolution

 

col1col2col3col4col5col6datetime
EClarkIIIPBAYFULLFULLFULL18032:11.0
EClarkIIIPBAYFULLFULLFULL043:58.0


Query:

 

SELECT col1,col2,col3,col4,col5,SUM(col6),row_number OVER (PARTITION BY col1,col2,col3,col4,col5 order by datetime desc) as rownum

from table A
Group By col1,col2,col3,col4,col5
QUALIFY count(*) OVER (PARTITION BY col1,col2,col3,col4,col5 order by datetime desc)>1

where rownum=1;

 

Thanks,

Pavan

 

Teradata Employee

Re: Failure 3504:Selected non-aggregate values must be part of associated group

I think this is what you want:

SELECT col1,col2,col3,col4,col5,SUM(col6), Max(datetime)
from tableA
Group By col1,col2,col3,col4,col5
Having Count(col6) > 1;