• Article

Troubleshooting GROUP BY Errors

The following table provides a list of GROUP BY error messages and suggestions to help resolve them.

Error number

Error message

How to resolve the error

102

Incorrect syntax near ','.

Rewrite the query so the grouping sets appear in the GROUP BY clause as part of an explicit GROUPING SETS list. For example, GROUP BY C1, (C2,…, Cn) will raise this error. Rewrite the query as GROUP BY C1, GROUPING SETS( (C2,…, Cn) ) or GROUP BY C1, C2,…, Cn.

130

Cannot perform an aggregate function on an expression that contains an aggregate or a subquery.

Rewrite the query so a grouping function aggregate, or subquery does not appear as an argument of another grouping function or aggregate.

147

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Remove the grouping function or aggregate from the WHERE clause.

157

An aggregate may not appear in the set list of an UPDATE statement.

Remove the grouping function or aggregate from the set list of an UPDATE statement.

158

An aggregate may not appear in the OUTPUT clause.

Remove the grouping function or aggregate from the OUTPUT clause.

162

Invalid expression in the TOP clause.

Remove the grouping function or aggregate from the TOP clause.

174

The GROUPING function requires 1 argument(s).

Modify the argument list for the GROUPING () function so there is exactly 1 argument.

175

An aggregate may not appear in a computed column expression or check constraint.

Remove the grouping function or aggregate from computed columns and CHECK constraints in the DDL statement.

189

The GROUPING_ID function requires 0 to 32 arguments.

Reduce the number of arguments for the GROUPING_ID () function to 32 or less.

1015

An aggregate cannot appear in an ON clause unless it is in a subquery contained in a HAVING clause or select list, and the column being aggregated is an outer reference.

Remove the grouping function or aggregate from the ON clause.

1028

The CUBE, ROLLUP, and GROUPING SETS options are not allowed in a GROUP BY ALL clause.

Rewrite the query so the GROUP BY ALL option is not specified with the GROUPING SETS, CUBE, ROLLUP, WITH CUBE, or WITH ROLLUP keywords.

4101

Aggregates on the right side of an APPLY cannot reference columns from the left side.

Remove the grouping function or aggregate from the right side of APPLY clause.

4113

GROUPING | GROUPING_ID is not a valid windowing function, and cannot be used with the OVER clause.

Rewrite the query so the GROUPING () or GROUPING_ID () function is not used with an OVER clause.

4142

Aggregates are not allowed in the RECEIVE list.

Remove the grouping function or aggregate from the RECEIVE list.

5310

Aggregates are not allowed in the VALUES list of an INSERT statement.

Remove the grouping function or aggregate from the VALUES list of an INSERT statement.

8161

Argument [n] of the [GROUPING | GROUPING_ID] function does not match any of the expressions in the GROUP BY clause.

Check to make sure that each argument of the GROUPING or GROUPING_ID function matches a grouping element in the GROUP BY clause and all arguments of the GROUPING or GROUPING_ID function have the same scope.

In the following example, the reference to table T (from the main query) in the subquery will raise an error.

SELECT T.b, SUM(T.x)
FROM T
GROUP BY T.b 
HAVING EXISTS
(SELECT 1
FROM T1
GROUP BY T1.a
HAVING 
GROUPING_ID(T1.a, T.b) = 1
)

8661

Cannot create the clustered index <indexname> on view <viewname> because the index key includes columns that are not in the GROUP BY clause. Consider eliminating columns that are not in the GROUP BY clause from the index key.

You cannot create an indexed view when the view definition contains a GROUP BY clause that only contains the element (), grand total.

10119

Cannot create the clustered index <indexname> on view <viewname> because it contains a CUBE, ROLLUP, or GROUPING SETS operator. Consider not indexing this view.

An indexed view cannot be created when the view definition contains a general GROUP BY clause.

10702

The WITH CUBE and WITH ROLLUP options are not permitted with a ROLLUP, CUBE, or GROUPING SETS specification.

Rewrite the query so there is no combination of the non-ISO WITH CUBE or WITH ROLLUP keywords and the ISO compatible GROUPING SETS, CUBE, or ROLLUP keywords.

10703

Too many grouping sets. The maximum number is 4096.

Reduce the number of grouping sets in a general GROUP BY clause to 4096 or less.

10706

Too many expressions are specified in the GROUP BY clause. The maximum number is 32 when grouping sets are supplied.

Reduce the distinct grouping elements in a general GROUP BY clause to 32 or less.

10708

The CUBE () and ROLLUP() grouping constructs are not allowed in the current compatibility mode. They are only allowed in 100 mode or higher.

You cannot use CUBE () and ROLLUP () in 90 compatibility mode. Use WITH CUBE, WITH ROLLUP, or GROUPING SETS syntax.