SQL GROUP BY Clause

The GROUP BY clause combines rows that have field values in common into single rows for the SELECT statement. The syntax is as follows:

  GROUP BY field_reference [, field reference...]

You can use a GROUP BY clause to cause an aggregation process to be repeated once for each group of similar rows. Similarity between rows is determined by the distinct values (or combination of values) in the fields specified in the GROUP BY. For instance, a query with a SUM function produces a result set with a single line with the total of all the values for the field used in the SUM function. But when a GROUP BY clause is added, the SUM function performs its summing action once for each group of rows. In statements that support a GROUP BY clause, the use of a GROUP BY clause is optional. A GROUP BY clause becomes necessary when both aggregated and non-aggregated fields are included in the same SELECT statement.

In the statement below, the SUM function produces one subtotal of the GrossAmount field for each distinct value in the CustomerCode field, that is, one subtotal for each Customer:

  SELECT CustomerCode, SUM(GrossAmount) FROM INHEAD GROUP BY CustomerCode

The value for the GROUP BY clause is a comma-separated list of fields. Each field in this list must meet the following criteria:

• Be in one of the tables specified in the FROM clause of the query.
• Also be in the SELECT clause of the query.
• Cannot have an aggregate function applied to it (in the SELECT clause).

When a GROUP BY clause is used, all table fields in the SELECT and ORDER BY clauses of the query must meet at least one of the following criteria, or it cannot be included in the SELECT or ORDER BY clause:

• Be in the GROUP BY clause of the query.
• Be the subject of an aggregate function.

Literal values in the SELECT clause are not subject to the preceding criteria and are not required to be in the GROUP BY clause in addition to the SELECT clause.

The distinctness of rows is based on the fields in the field list specified. All rows with the same values in these fields are combined into a single line (or logical group). Fields that are the subject of an aggregate function have their values across all rows in the group combined. All fields not the subject of an aggregate function retain their value and serve to distinctly identify the group. For example, in the SELECT statement below, the values in the GrossAmount field are aggregated (totalled) into groups based on distinct values in the CustomerCode field. This produces total gross amount for each Customer:

  SELECT C.CustomerCode, SUM(H.GrossAmount)

  FROM ARCUST C, INHEAD H

  WHERE C.CustomerCode = H.CustomerCode

  GROUP BY C.CustomerCode

  ORDER BY C.CustomerCode

A field can be referenced in a GROUP BY clause by a field correlation name, instead of actual field names. The statement below forms groups using the first field, CustomerCode, represented by the field correlation name CC:

  SELECT C.CustomerCode CC, SUM(H.GrossAmount) AS TotalGross

  FROM ARCUST C, INHEAD H

  WHERE C.CustomerCode = H.CustomerCode

  GROUP BY CC

  ORDER BY 1