GROUP_BY_AGGREGATE error class

SQLSTATE: 42903

Aggregate functions are not allowed in GROUP BY, but found <sqlExpr>.

Parameters

  • sqlExpr: Expression containing an aggregate function.

Explanation

The purpose of the `GROUP BY` clause is to identify the set of distinct groups. Each group of rows is then collapsed into a single row using aggregate functions in the SELECT list. Finally, you can then filter the grouped rows using the `HAVING` clause.

The sqlExpr is located in the GROUP BY clause instead of the `SELECT` list or HAVING clause.

Mitigation

The mitigation of the error depends on the cause:

  • Did you specify the correct function?

    Replace sqlExpr with an appropriate function which is not an aggregate function.

  • Do you intend to aggregate the expression?

    Remove the expression from the GROUP BY and add it to the SELECT list.

  • Do you intend to filter on the aggregated expression?

    Remove the expression from the GROUP BY clause and add it using a BOOLEAN operator to the HAVING clause.

Examples 

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