Entering content frame

Set Function (set_function_spec) Locate the document in its SAP Library structure

There is a series of functions that can be applied to a set of values (rows) as an argument and supply a result. These functions are referred to as set functions (set_function_spec).

Syntax

<set_function_spec> ::= COUNT(*)
| <all_function>
| <distinct_function>

<all_function> ::= <set_function_name> ( ALL <expression> )

<distinct function>::= <set function name> ( DISTINCT <expression> )

<set_function_name> ::= AVG | COUNT | MAX | MIN | STDDEV | SUM | VARIANCE

Explanation

Set functions operate across groups of values but only return one value. The result comprises one row. If a set function is used in a statement, a similar function must also be applied to each of the other columns in the request. This, however, does not apply to columns that were grouped using GROUP BY. In this case, the value of the set function can be defined for each group.

The argument of a DISTINCT function or an ALL function is a result table or a group (the result table can be grouped using a GROUP condition).

With the exception of the COUNT(*) function, no NULL values are included in the calculation.

No locks are set for certain set functions, irrespective of the isolation level specified when the user connected to the database.

Example

SELECT SUM(price) sum_price, MIN(price) min_price, FIXED(AVG(price),7,2) avg_price, MAX(price) max_price, COUNT(*) number
  FROM room
    WHERE type = 'single'

Calculations for single room: sum of the prices, lowest price, average price, highest price, number of single rooms

SUM_PRICE

MIN_PRICE

AVG_PRICE

MAX_PRICE

NUMBER

1450

45

96.67

160

15

 

SELECT COUNT(*) number, FIXED (AVG(price),7,2) avg_price
  FROM room
    WHERE type = 'suite'

How many rooms that are suites does the list contain? What is the average price?

NUMBER

AVG_PRICE

8

475

 

SELECT COUNT(*) number
  FROM customer

How many customers are there?

NUMBER

15

<set_function_name>

The set functions are classified by the following set function names:

AVG

The result of AVG is the arithmetical mean of the values of the argument. AVG can only be applied to numeric values. The result has the data type FLOAT(38).

COUNT

- COUNT(*) supplies the total number of values (rows in a result table or group).

- COUNT(DISTINCT <expression>) supplies the total number of different values (number of values in the argument of the DISTINCT function).

- COUNT(ALL <expression>) supplies the number of values that differ from the NULL value (number of values in the argument of the ALL function).

The result has the data type FIXED(10).

MAX

The result of MAX is the largest value of the argument.

MIN

The result of MIN is the smallest value of the argument.

STDDEV

The result of STDDEV is the standard deviation of the values of the argument. STDDEV can only be applied to numeric values. The result has the data type FLOAT(38).

SUM

The result of SUM is the sum of the values of the argument. SUM can only be applied to numeric values. The result has the data type FLOAT(38).

VARIANCE

The result of VARIANCE is the variance of the values of the argument. VARIANCE can only be applied to numeric values. The result has the data type FLOAT(38).

ALL/DISTINCT Function

 

Result of the ALL/DISTINCT Function

The set of values is empty and the ALL/DISTINCT function is applied to the entire result table

The set functions AVG, MAX, MIN, STDDEV, SUM, VARIANCE supply the NULL value as their result.

The set function COUNT supplies the value 0.

There is no group to which the ALL/DISTINCT function can be applied.

The result is an empty table.

The set of values contains at least one special NULL value.

Special NULL value

<all_function>

The ALL function (all_function) is a set function that removes the NULL values.

The argument of an ALL function is a set of values that is calculated as follows:

       1.      A result table or group (the result table can be grouped with a GROUP condition) is formed.

       2.      The expression (expression) is applied to each row in this result table or group.
The expression must not contain a set function.

       3.      All NULL values are removed. Special NULL values are not removed; two special NULL values are regarded as identical.

The ALL function is executed taking into account the relevant set function name (set_function_name) for the set of values.

The result of an ALL function is independent of whether the keyword ALL is specified or not.

<distinct_function>

The DISTINCT function (distinct_function) is a set function that removes duplicated values and all NULL values.

The argument of a DISTINCT function is a set of values that is calculated as follows:

...

       1.      A result table or group (the result table can be grouped with a GROUP condition) is formed.

       2.      The expression (expression) is applied to each row in this result table or group.
The expression must not contain a set function.

       3.      All of the NULL values and duplicated values are removed (DISTINCT). Special NULL values are not removed; two special NULL values are regarded as identical.

The DISTINCT function is executed taking into account the relevant set function name (set_function_name) for this set of values.

Example

SELECT COUNT(DISTINCT zip) number_zip FROM customer

In how many cities with different zip codes do customers live?

NUMBER_ZIP

12

 

 

Leaving content frame