GROUP BY and WITH...BY clauses may not contain aggregate functions ?

Database

GROUP BY and WITH...BY clauses may not contain aggregate functions ?


I execute the SQL below and I get the error message. There no is aggregate in the 'GROUP BY' clause as the message claims. The SQL works fine on Oracle and other databases.

SELECT COUNT(*) AS c1
FROM "XXX"."MY_CUST" t1
GROUP BY 1

16:14:00 [SELECT - 0 rows, 0.156 sec] [NCR] [Teradata DBMS] : GROUP BY and WITH...BY clauses may not contain aggregate functions.
3 REPLIES
Teradata Employee

Re: GROUP BY and WITH...BY clauses may not contain aggregate functions ?

When you say "GROUP BY 1", the 1 refers to the first expression in the SELECT list, namely COUNT(*), which is why you get this error. If all the expressions are aggregates you can just omit the GROUP BY clause.

Re: GROUP BY and WITH...BY clauses may not contain aggregate functions ?

Thanks very much for your response - that explains it.

There is a subtle difference between GROUP BY and leaving off the GROUP BY. If I leave off the GROUP BY, when there are no matching rows, the result is 0. By adding the GROUP BY, when there are no matching rows, the result is empty. The second way matches the semantics of XQuery.

Mike R.
Teradata Employee

Re: GROUP BY and WITH...BY clauses may not contain aggregate functions ?

True. If you want the "empty return" behavior, you can use
GROUP BY
just as long as the expression is not a simple integer constant. That case has special meaning in Teradata. You could use a non-numeric or decimal constant, a function or CAST applied to a constant, NULL, etc.