GROUP_BY_AGGREGATE 错误类
GROUP BY 中不允许使用聚合函数,但发现了 <sqlExpr>
。
参数
- sqlExpr:包含聚合函数的表达式。
说明
GROUP BY
子句的用途是标识一组不同的组。
然后使用 SELECT
列表中的聚合函数将每组行折叠成一行。
最后,可以使用 HAVING
子句筛选已分组的行。
sqlExpr
位于 GROUP BY
子句中,而不是位于 SELECT
列表或 HAVING
子句中。
缓解措施
错误的缓解方法取决于具体原因:
是否指定了正确的函数?
请将
sqlExpr
替换为不是聚合函数的适当函数。你是否要聚合表达式?
从
GROUP BY
中删除表达式并将其添加到SELECT
列表。你是否要对聚合表达式进行筛选?
从
GROUP BY
子句中删除表达式,并使用BOOLEAN
运算符将其添加到HAVING
子句。
示例
-- An aggregation in the GROUP BY clause
> SELECT a FROM VALUES(1, 2), (1, 3) AS T(a, b) GROUP BY a, sum(b);
[GROUP_BY_AGGREGATE] Aggregate functions are not allowed in GROUP BY, but found sum(T.b).; line 1 pos 58;
-- Move the expression to the SELECT list
> SELECT a, sum(b) FROM VALUES(1, 2), (1, 3) AS T(a, b) GROUP BY a;
1 5
-- An aggregation in the GROUP BY clause
> SELECT a, sum(b) FROM VALUES(1, 2), (1, 3), (2, 1) AS T(a, b) GROUP BY a, sum(b) > 1;
[GROUP_BY_AGGREGATE] Aggregate functions are not allowed in GROUP BY, but found (sum(T.b) > CAST(1 AS BIGINT)).; line 1 pos 74;
-- Move the expression to the HAVING clause
> SELECT a, sum(b) FROM VALUES(1, 2), (1, 3), (2, 1) AS T(a, b) GROUP BY a HAVING sum(b) > 1;
1 5