All Forums Database
mikereiche 3 posts Joined 06/07
13 Jun 2007
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.

Fred 1096 posts Joined 08/04
13 Jun 2007

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.

mikereiche 3 posts Joined 06/07
14 Jun 2007

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.

Fred 1096 posts Joined 08/04
14 Jun 2007

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.

You must sign in to leave a comment.