This function is an alias for the STDDEV_SAMP function. See STDDEV_SAMP function [Aggregate].

This function computes the standard deviation of a population consisting of a numeric expression, as a DOUBLE.

The following query returns a result set that shows the employees whose salary is one standard deviation greater than the average salary of their department. Standard deviation is a measure of how much the data varies from the mean.

SELECT *
FROM ( SELECT 
    Surname AS Employee, 
    DepartmentID AS Department, 
    CAST( Salary as DECIMAL( 10, 2 ) ) 
        AS Salary,
    CAST( AVG( Salary ) 
        OVER ( PARTITION BY DepartmentID ) AS DECIMAL ( 10, 2 ) ) 
        AS Average,
    CAST( STDDEV_POP( Salary )
        OVER ( PARTITION BY DepartmentID ) AS DECIMAL ( 10, 2 ) ) 
        AS StandardDeviation
    FROM Employees
    GROUP BY Department, Employee, Salary )
    AS DerivedTable
WHERE Salary > Average + StandardDeviation
ORDER BY Department, Salary, Employee;

The table that follows represents the result set from the query. Every department has at least one employee whose salary significantly deviates from the mean.

Employee Scott earns $96,300.00, while the departmental average is $58,736.28. The standard deviation for that department is $16,829.00, which means that salaries less than $75,565.88 (58736.28 + 16829.60 = 75565.88) fall within one standard deviation of the mean. At $96,300.00, employee Scott is well above that figure.

This example assumes that Surname and Salary are unique for each employee, which isn't necessarily true. To ensure uniqueness, you could add EmployeeID to the GROUP BY clause.

The following statement lists the average and variance in the number of items per order in different time periods:

SELECT YEAR( ShipDate ) AS Year,
    QUARTER( ShipDate ) AS Quarter,
    AVG( Quantity ) AS Average,
    STDDEV_POP( Quantity ) AS Variance
FROM SalesOrderItems
GROUP BY Year, Quarter
ORDER BY Year, Quarter;

This query returns the following result:

For more information about the syntax for this function, see STDDEV_SAMP function [Aggregate].

This function computes the standard deviation of a sample consisting of a numeric expression, as a DOUBLE. For example, the following statement returns the average and variance in the number of items per order in different quarters:

SELECT YEAR( ShipDate ) AS Year,
    QUARTER( ShipDate ) AS Quarter,
    AVG( Quantity ) AS Average,
    STDDEV_SAMP( Quantity ) AS Variance
FROM SalesOrderItems
GROUP BY Year, Quarter
ORDER BY Year, Quarter;

This query returns the following result:

For more information about the syntax for this function, see STDDEV_POP function [Aggregate].

This function is an alias for the VAR_SAMP function. See VAR_SAMP function [Aggregate].

This function computes the statistical variance of a population consisting of a numeric expression, as a DOUBLE. For example, the following statement lists the average and variance in the number of items per order in different time periods:

SELECT YEAR( ShipDate ) AS Year,
    QUARTER( ShipDate ) AS Quarter,
    AVG( Quantity ) AS Average,
    VAR_POP( quantity ) AS Variance
FROM SalesOrderItems
GROUP BY Year, Quarter
ORDER BY Year, Quarter;

This query returns the following result:

If VAR_POP is computed for a single row, then it returns the value 0.

For more information about the syntax for this function, see VAR_POP function [Aggregate].

This function computes the statistical variance of a sample consisting of a numeric expression, as a DOUBLE.

For example, the following statement lists the average and variance in the number of items per order in different time periods:

SELECT YEAR( ShipDate ) AS Year,
    QUARTER( ShipDate ) AS Quarter,
    AVG( Quantity ) AS Average,
    VAR_SAMP( Quantity ) AS Variance
FROM SalesOrderItems
GROUP BY Year, Quarter
ORDER BY Year, Quarter;

This query returns the following result:

If VAR_SAMP is computed for a single row, then it returns NULL.

For more information about the syntax for this function, see VAR_SAMP function [Aggregate].