GROUP_BY_AGGREGATE error class

  • Article

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