SUM

Returns the sum of all the values, or only the DISTINCT values, of the specified field-expression.

Arguments:

DISTINCT

Specifies that SUM returns the sum of unique values.
DISTINCT can only be used when the query does not make use of the GROUP BY clause.

ALL

Applies the aggregate function to all values. ALL is the default.

<field_expr>

The field-expression whose values are to be summed.
The field-expression data type must be INTEGER or REAL.

Return Type:

INTEGER or REAL, depending on the argument field-expression.

Remarks:

• NULL values are ignored by the SUM aggregate function.
• Aggregate functions are allowed as field-expressions only in the SELECT, HAVING, and ORDER BY clauses.
• The arguments of an aggregate function can not reference other aggregate functions.
• The arguments of an aggregate function can not reference the following functions:
  • SEQUENCE
  • OUT_ROW_NUMBER
• DISTINCT is allowed in aggregate functions only when there is no GROUP BY clause.

Examples:

A. SUM

The following query returns the total number of bytes for executable files in the "system32" directory, using the FS input format:

SELECT SUM(Size)
FROM C:\windows\system32\*.*
WHERE TO_LOWERCASE(EXTRACT_EXTENSION(Name)) = 'exe'

B. SUM and GROUP BY

The following query returns the total number of bytes sent for each page extension logged in the specified IIS W3C log file:

SELECT  TO_LOWERCASE(EXTRACT_EXTENSION(cs-uri-stem)) AS PageType,
        SUM(sc-bytes)
FROM ex031118.log
GROUP BY PageType

See also:

Aggregate Functions

Aggregating Data Within Groups

© 2004 Microsoft Corporation. All rights reserved.