Analytical Functions

Analytical functions include simple aggregates, window functions, and numeric functions.

  • Simple aggregates — AVG, COUNT, MAX, MIN, and SUM, STDDEV, and VARIANCE

    Note: You can use all simple aggregates, except the Grouping() function, with an OLAP windowed function.

  • Window functions:

    • Windowing aggregates — AVG, COUNT, MAX, MIN, and SUM.

    • Ranking functions — RANK, DENSE_RANK, PERCENT_RANK, ROW_NUMBER, and NTILE.

    • Statistical functions — STDDEV, STDDEV_SAMP, STDDEV_POP, VARIANCE, VAR_SAMP, and VAR_POP.

    • Distribution functions — PERCENTILE_CONT and PERCENTILE_DISC.

    • Inter-row functions — LAG and LEAD.

  • Numeric functions — WIDTH_BUCKET, CEIL, and LN, EXP, POWER, SQRT, and FLOOR.

    Note: The ranking and inverse distribution analytical functions are not supported by Adaptive Server Enterprise.

This table lists the analytical functions and their parameters. Unlike some aggregate functions, you cannot specify DISTINCT in window functions.

Analytical functions

Function

Parameters

AVG

( { column-name | numeric-expr } )

COUNT

( * )

COUNT

( { column-name | expression } )

DENSE_RANK

()

GROUPING *

( { GROUPING group-by-expression } )

MAX

( { column-name | expression } )

MIN

( { column-name | expression } )

NTILE

( integer )

PERCENT_RANK

()

PERCENTILE_CONT

( numeric-expr )

PERCENTILE_DISC

( numeric-expr )

RANK

()

ROW_NUMBER

()

STDDEV

( [ ALL ] expression )

STDDEV_POP

( [ ALL ] expression )

STDDEV_SAMP

( [ ALL ] expression )

SUM

( { column-name | expression } )

VAR_POP

( [ ALL ] expression )

VAR_SAMP

( [ ALL ] expression )

VARIANCE

( [ ALL ] expression )

* The OLAP SQL standard allows Grouping() in GROUP BY CUBE, or GROUP BY ROLLUP operations only.

Parent topic: SQL Functions
Related reference
Aggregate Functions