15.10 - GROUP BY - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

An introduction to an optional reference to one or more expressions in the select expression list used to group rows in the result set.

A GROUP BY clause is valid when coded as part of a seed statement.

GROUP BY is never valid when coded as part of a recursive statement.

ordinary_grouping_set
A column expression by which the rows returned by the request are grouped.
ordinary_grouping_set includes:
  • column_name specifies a set of column names drawn from the list of tables specified in the FROM clause of the SELECT statement that is used in the GROUP BY clause to specify the columns by which data is to be grouped.
You cannot include LOB columns in the grouping expression.
  • column_position specifies the sequential numeric position of columns within the column_list clause of the SELECT statement that is used in the GROUP BY clause to specify the order in which data is to be grouped.
This must be a positive integer.
You cannot include LOB columns in the grouping expression.
Use of column_position is a Teradata extension to the ANSI SQL-2011 standard.
  • expression specifies any list of valid SQL expressions specified for the GROUP BY clause.
You can specify column_name, column_position, and expression either as individual entries or as a list.
You cannot include LOB columns in the ordinary grouping set.
Use of expression is a Teradata extension to the ANSI SQL-2011 standard.
empty_grouping_set
A contiguous LEFT PARENTHESIS, RIGHT PARENTHESIS pair with no argument. In general, this syntax is used to request a grand total.
rollup_list
A ROLLUP expression that reports result rows in a single dimension with one or more levels of detail. See SQL Data Manipulation Language, B035-1146 for further information.
cube_list
A CUBE expression that reports result rows in multiple dimensions with one or more levels of detail. See SQL Data Manipulation Language, B035-1146 for further information.
grouping_sets_specification
A GROUPING SETS expression that reports result rows in one of two ways:
  • As a single dimension, but without a full ROLLUP.
  • As multiple dimensions, but without a full CUBE.
See SQL Data Manipulation Language, B035-1146 for further information.

Example: GROUP BY Clause Usage

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