7

I have two tables in Teradata: Table_A and Table_B. Between them is LEFT JOIN. Afterwards I am making SELECT statement which contains attributes from both tables:

SELECT
attribute_1
attribute_2
...
attribute_N

Afterwords, I am using SUM functions to do certain calculations. These functions look something like this:

SUM (
CASE WHEN Attribute_1 > 2 THEN attribute_2*1.2
ELSE 0
End

(in this example attributes in the select part are used).

But I also use in CASE part attributes which are not in the select statement - something liek this:

SUM (
CASE WHEN Attribute_X > 2 THEN attribute_Y*1.2
ELSE 0
End

Of course at the end I am doing GROUP BY 1,2,...,N

The error I am getting is "Selected non-aggregate values must be part of the associated group." Furtheremore, I have checked billion times the number of the selected attributes in the SELECT part, and it is N. The question is - why am I getting this error? Is it because I am using in the SUM part i.e. CASE part attributes (attribute_X and attribute_Y) which are not included in the SELECT part?

Blueprint of the end-statement looks sthg. like this:

INSERT INTO table_new

SELECT
attribute_1,
attribute_2,
...
attribute_N,

SUM (
CASE WHEN Attribute_1 > 2 THEN attribute_2*1.2
ELSE 0
End
) as sum_a,

SUM (
CASE WHEN Attribute_X > 2 THEN attribute_Y*1.2
ELSE 0
End
) as sum_X

FROM table_a LEFT JOIN table_B
ON ...

GROUP BY 1,2,...,N
topchef
  • 19,091
  • 9
  • 63
  • 102
Adam
  • 2,347
  • 12
  • 55
  • 81

2 Answers2

11

The error message suggests that you have not included all the non-aggregate columns listed in your SELECT statement in your GROUP BY expression. I'm guessing that you have more columns listed than you have "place holders".

The best way to avoid this is to explicitly name all the columns and not use the "relative positioning" syntax. In other words, rather than using GROUP BY 1,2,...N use:

GROUP BY
   attribute_1,
   attribute_2,
   ...
   attribute_N

If that does not fix your problem, modify your question and show a complete query that is not working.

BellevueBob
  • 9,498
  • 5
  • 29
  • 56
  • I did as you described - still I am getting the same message. My assumption is that even those attributes that are used in the CASE part (i.e. SUM part - aggragate function), have to be included in the SELECT part. That implies that they have to be included in the GROUP BY part (even though I do not need them). That should mean that I have to do another SELECT and another SUM in order to get exactly data I need. – Adam Jun 30 '13 at 10:41
  • 1
    I have found the error - the SUM part was composed of more sub-parts. For example "amount - SUM(...) + SUM(...)". I had ti include the attributes in the "amount" part. – Adam Jun 30 '13 at 12:24
1

I have found the error - the SUM part was composed of more sub-parts. For example "amount - SUM(...) + SUM(...)". I had ti include the attributes in the "amount" part.

Adam
  • 2,347
  • 12
  • 55
  • 81