All Forums General
013.kumarpankaj 2 posts Joined 07/14
16 Jul 2014
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
 

Regards, Pankaj Kumar
dnoeth 4628 posts Joined 11/04
16 Jul 2014

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

Dieter

013.kumarpankaj 2 posts Joined 07/14
16 Jul 2014

Thanks Dieter.
We got this working.

Regards,
Pankaj Kumar

You must sign in to leave a comment.