Aggregate Functions

Aggregate functions perform a calculation on a set of values but return a single, summarizing value.

Aggregate functions are often used with the GROUP BY clause.
When used without a GROUP BY clause, aggregate functions perform calculations on the entire set of input records, returning a single summarizing value for the whole set.
When used with a GROUP BY clause, aggregate functions perform calculations on each set of group records, returning a summarizing value for each group.

Functions:

COUNT

Returns the number of items in a group.
For more information, see COUNT.

SUM

Returns the sum of the values of the specified field-expression.
For more information, see SUM.

AVG

Returns the average across the values of the specified field-expression.
For more information, see AVG.

MAX

Returns the maximum value among the values of the specified field-expression.
For more information, see MAX.

MIN

Returns the minimum value among the values of the specified field-expression.
For more information, see MIN.

PROPCOUNT

Returns the ratio of the COUNT aggregate function calculated on a group to the COUNT aggregate function calculated on a hierarchically higher group.
For more information, see PROPCOUNT.

PROPSUM

Returns the ratio of the SUM aggregate function calculated on a group to the SUM aggregate function calculated on a hierarchically higher group.
For more information, see PROPSUM.

GROUPING

Returns a value of 1 when the row is added by the ROLLUP operator of the GROUP BY clause, or 0 when the row is not the result of ROLLUP.
The GROUPING aggregate function is allowed only when the GROUP BY clause contains the ROLLUP operator.
For more information, see GROUPING.

Remarks:

• 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. COUNT(*)

The following query returns the total number of events in the System event log:

SELECT COUNT(*)
FROM System

B. COUNT(DISTINCT)

The following query returns the total number of distinct event source names in the System event log:

SELECT COUNT(DISTINCT SourceName)
FROM System

C. COUNT(*) and GROUP BY

The following query returns the total number of events generated by each event source in the System event log:

SELECT SourceName, COUNT(*)
FROM System
GROUP BY SourceName

D. 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

E. PROPCOUNT(*), GROUP BY, and HAVING

The following query returns the pages that represent more than 10% of the requests in the specified IIS W3C log file:

SELECT cs-uri-stem
FROM ex031118.log
GROUP BY cs-uri-stem
HAVING PROPCOUNT(*) > 0.1

See also:

Functions
SELECT
HAVING
GROUP_BY

Aggregating Data Within Groups
Calculating Percentages

© 2004 Microsoft Corporation. All rights reserved.