Reporting aggregation functions

Window functions also support the following aggregates:

• COUNT
• SUM
• MIN
• MAX
• AVG
• STDEV
• VAR
• STDEVP
• VARP

The syntax:

<AggregateFunction>( [DISTINCT] <expression>) [<OVER_clause>]

Note:

• By default, aggregate functions, except COUNT, ignore null values.
• When aggregate functions are specified along with the OVER clause, the ORDER BY clause is not allowed in the OVER clause.

SUM

The following example adds a total salary by department to each input row:

@result=
    SELECT
        *,
        SUM(Salary) OVER( PARTITION BY DeptName ) AS TotalByDept
    FROM @employees;

COUNT

The following example adds an extra field to each row to show the total number employees in each department.

@result =
    SELECT 
        *,
        COUNT(*) OVER(PARTITION BY DeptName) AS CountByDept
    FROM @employees;

MIN and MAX

The following example adds an extra field to each row to show the lowest salary of each department:

@result =
    SELECT
        *,
        MIN(Salary) OVER ( PARTITION BY DeptName ) AS MinSalary
    FROM @employees;