所选的非聚合值必须是关联组的一部分

时间:2022-05-29 19:22:30

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:

在Teradata中有两个表:Table_A和Table_B。它们之间是左连接。然后我做SELECT语句,其中包含来自两个表的属性:

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).

(在本例中使用select部分中的属性)。

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

当然最后我要做的是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?

我得到的错误是“选择的非聚合值必须是关联组的一部分”。而且,我已经检查了十亿倍于SELECT部分中所选属性的数量,它是n,问题是——为什么我得到这个错误?是因为我在SUM部分中使用了CASE部件属性(attribute_X和attribute_Y),而这些属性并不包括在SELECT部分中吗?

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

最终声明的蓝图看起来像sthg。是这样的:

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

2 个解决方案

#1


6  

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".

错误消息表明,您没有按表达式在组中的SELECT语句中列出的所有非聚合列。我猜你列出的专栏比你列出的“位持有人”要多。

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 1,2,……N使用:

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.

如果这不能解决您的问题,请修改您的问题并显示一个不工作的完整查询。

#2


0  

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.

我发现了错误-和部分由更多的子部分组成。例如“amount - SUM(…)+ SUM(…)”。我让ti在“数量”部分包含属性。

#1


6  

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".

错误消息表明,您没有按表达式在组中的SELECT语句中列出的所有非聚合列。我猜你列出的专栏比你列出的“位持有人”要多。

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 1,2,……N使用:

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.

如果这不能解决您的问题,请修改您的问题并显示一个不工作的完整查询。

#2


0  

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.

我发现了错误-和部分由更多的子部分组成。例如“amount - SUM(…)+ SUM(…)”。我让ti在“数量”部分包含属性。