Similarly to aggregates and ordered analytic functions, a GROUP BY clause is valid when coded as part of the seed statement in a recursive view definition, but is not valid when coded as part of the recursive statement.
The first example demonstrates correct usage of a GROUP BY clause. In this example, the GROUP BY is coded as part of the seed statement.
CREATE RECURSIVE VIEW aggregation (source,destination,mycount) AS ( SELECT source, destination, 0 AS mycount FROM flights GROUP BY source, destination UNION ALL SELECT in1.source, out1.destination, in1.mycount + 1 FROM aggregation AS in1, flights AS out1 WHERE in1.destination = out1.source AND in1.mycount <=100);
The second example demonstrates non-valid usage of a GROUP BY clause. In this example, the GROUP BY clause is coded as part of the recursive statement.
CREATE RECURSIVE VIEW aggregation (source,destination,mycount) AS ( SELECT source, destination, 0 AS mycount FROM flights UNION ALL SELECT in1.source, out1.destination, in1.mycount + 1 FROM aggregation AS in1, flights AS out1 WHERE in1.destination = out1.source AND in1.mycount <=100 GROUP BY in1.source, out1.destination);