Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Selected non-aggregate values must be part of the associated group

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
like image 781
Adam Avatar asked Jun 29 '13 09:06

Adam


People also ask

Which non-aggregate values are part of the Associated Group?

15.00 - 3504 Selected non-aggregate values must be part of the associated group. - Teradata Database 15.00 - 3504 Selected non-aggregate values must be part of the associated group.

Why is my aggregate query not working?

. .This error occurs when an aggregate query includes a non-aggregate expression in the SELECT list, WHERE clause, ORDER BY clause, HAVING clause or WITH list that is not also exactly specified in the corresponding GROUP BY or WITH...BY clause.

What is an exception in group by?

Exception – you do not have to use the GROUP BY clause during grouping data without using additional attributes, but it is used rarely. Selected Non-Aggregate Values Must Be Part Of The Associated Group – Means Invalid Number Of Attributes

What is the difference between 5479 and 5480 aggregates?

5478 Aggregates are allowed only with Window Functions. 5479 Ordered Analytical Functions not allowed in WHERE Clause. 5480 Ordered Analytical Functions can not be nested.


1 Answers

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.

like image 186
BellevueBob Avatar answered Dec 03 '22 15:12

BellevueBob