Query Aggregation & group by issue

General

Query Aggregation & group by issue

We are not able to fire the below query, this throws an error as group by not working on aggregate function :

select CASE WHEN (TRIM(col1) NOT IN ('A','B','C') AND lkp.col2='X') THEN SUM(src.amt)*1000*-1 

      WHEN (TRIM(col1) NOT IN ('A','B','C') AND lkp.col2 NOT IN ('X')) THEN SUM(src.amt)*1000 

      WHEN (TRIM(col1) IN ('A','B','C') AND lkp.col2='X') THEN SUM(src.amt)*-1 

      WHEN (TRIM(col1) IN ('A','B','C') AND lkp.col2 NOT IN ('X')) THEN SUM(src.amt) END

       FROM SOURCE_TABLE src

       INNER JOIN LOOKUP_TABLE lkp

        ON src.col1=lkp.col3

         group by 1;

 

This doesn't works with or without group by 1

 

Error without group by :

[NCR] [Teradata DBMS] : Selected non-aggregate values must be part of the associated group. [SQL State=HY000, DB Errorcode=3504]

 

Error with group by :

[NCR] [Teradata DBMS] : GROUP BY and WITH...BY clauses may not contain aggregate functions. [SQL State=HY000, DB Errorcode=3625]

 

Please comment on this.

 

Thanks Pankaj

 

2 REPLIES
Senior Apprentice

Re: Query Aggregation & group by issue

Hi Pankaj,

I don't know what you actually want, but this might be correct:

select SUM( CASE WHEN (TRIM(col1) NOT IN ('A','B','C') AND lkp.col2='X') THEN src.amt*1000*-1 
WHEN (TRIM(col1) NOT IN ('A','B','C') AND lkp.col2 NOT IN ('X')) THEN src.amt*1000
WHEN (TRIM(col1) IN ('A','B','C') AND lkp.col2='X') THEN src.amt*-1
WHEN (TRIM(col1) IN ('A','B','C') AND lkp.col2 NOT IN ('X')) THEN src.amt END)
FROM SOURCE_TABLE src
INNER JOIN LOOKUP_TABLE lkp
ON src.col1=lkp.col3
;

This will return a single sum based on the WHEN-conditions. 

Btw, instead of src.amt*-1 you can simply write -src.amt

Re: Query Aggregation & group by issue

Thanks Dieter.

We got this working.