Example: GROUP BY Clause Usage - Advanced SQL Engine - Teradata Database

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);