Analytic
functions are used in grouped queries and window functions. An example is the
SUM function. You might think such functions can be stand alone such as SELECT
SUM(salary) FROM employees. However, in that scenario, that is a grouped query
with a single group, upon which the analytic SUM function is applied.
You can also
define a grouping set using the GROUP BY keywords. Then when you query an
analytic function such as SUM, it operates on each of the values of the set. If
you are doing a GROUP BY on a single column, every distinct value of that
column forms a group upon which the analytic function is applied.
Note that some analytic functions will ignore
NULL values. If you are doing a COUNT on some column, the function will skip
over records where that column value is NULL. There are also the possibility of
user defined aggregates (UDAs). But they require coding in an external .NET
language.
