Additions to the SELECT Statement

There are several additional clauses that can be used in the SELECT statement which allow you to obtain data from the database in an efficient and consolidated manner.

Aggregate Functions

In a SELECT statement, you can choose how many columns need to be listed as arguments of the given aggregate expressions.

In aggregate expressions, values from multiple rows are calculated to produce a single value. The DISTINCT addition prevents values from occurring more than once in the result set.

The Aggregate Expressions which can be used are as follows:

• MAX ( [DISTINCT] col )
  MAX ( [DISTINCT] col ) determines the maximum value of the col column in the result set or the current group.
• MIN ( [DISTINCT] col )
  MIN ( [DISTINCT] col ) determines the minimum value of the col column in the result set or the current group.
• AVG ( [DISTINCT] col )
  AVG ( [DISTINCT] col ) determines the average value of the col column in the result set or the current group. The data type of the column must be numeric.
• SUM ( [DISTINCT] col)
  SUM ( [DISTINCT] col) determines the total of the values of the col column in the result set or the current group. The data type of the column must be numeric.
• COUNT ( [DISTINCT] col )
  COUNT ( [DISTINCT] col ) determines the number of different values of the col column in the result set or the current group.
• COUNT (*)
  COUNT (*) determines the number of rows of the result set or the current group. In this case, no column name is specified.

The GROUP BY Clause

The GROUP BY addition (GROUP BY col1, col2) combines rows that have the same value combinations for the columns specified, into a single row in the result set.

When you use this technique, all columns that are not used in an aggregate function must also be listed in the GROUP BY clause.

GROUP BY and Aggregate

GROUP BY with COUNT

When you use INTO CORRESPONDING FIELDS in the SELECT statement, ensure that each aggregate function from the SELECT clause corresponds with an identically named field in the target structure or internal table. You can do this by assigning alias names to the aggregate functions using the AS addition in the column list.

Result of a GROUP BY Database Query

The figure displays an example of the results that would be returned by a database query using the GROUP BY clause.