How to use STDEVP Function in SQL Server

In this SQL Server tutorial, you will learn about the statistical STDEVP function SQL Server.

This function helps determine the variability in your data or the information that you have stored in the table. First, you will understand the ‘What is STDEVP() function?’, then its syntax.

After that, the standard deviation of data containing duplicate or unique values is computed using the STDEVP function in SQL Server. Ultimately, you will learn how to apply the STDEVP function in SQL Server on the table columns.

STDEVP Function in SQL Server

Suppose you are a statistician or data analyst who works daily with data to draw meaningful information from the existing, collected data or database containing tons of information or data. Think that you need to know the variation in your data set.

In that case, the STDEVP() function can be proven very helpful by computing the standard deviation of a population or the whole data or all data that you have. As you know, standard deviation shows the variability or dispersion of the entire data.

So, the STDEVP() function calculates and returns the population’s standard deviation (data in a table) in the database context.

The syntax is given below.

STDEV(ALL | DISTINCT expression)

Where,

  • STDEVP(): This is the function for finding the standard deviation of the population or table data.
  • column_name: Name of the column containing the data or values on which the STDEVP() function is applied. It can’t be the function or subqueries and the bit data type.
  • ALL | DISTINCT: When you use the STDEVP function in SQL Server with different options, such as ALL or DISTINCT, it computes the standard deviation differently.
    • STDEVP(ALL expression): When you use the STDEVP() function with the ALL option, it applies the STDEVP() function on all the values of the given expression (columns values), along with the duplicate values in the columns of the table.
    • STDEVP(DISTINCT expression): This one only applies the STDEVP() function on the unique values of the columns and does not include the duplicate ones.

The STDEVP () return type is a float, which means after calculating the standard deviation of the given set of values, it returns the numeric value, and the data type of that numeric value is always float type.

Let’s understand with an example, you have a sales table below.

STDEVP Function in SQL Server Sales Table

By looking at the sale_amount column in the above table, you want to compute the standard deviation over the values in the sale_amount column to know the sales amount variability.

For that, apply the STDEVP() function using the query below.

SELECT STDEVP(sale_amount) as StdOfSales FROM sales;
STDEVP Function in SQL Server Computing Standard Deviation

After executing the above query, the result set contains the sales amount standard deviation, 44324.12641881. Here, you must understand how the STDEVP() function is used on the sale_amount column to compute the standard deviation.

This is how you can use the STDEVP function in SQL Server.

STDEVP Function in SQL Server with ALL and DISTINCT

When the STDEVP function in SQL Server is used with options ALL and DISTINCT, it returns a different standard deviation because of the duplicate or unique values in the table columns.

First, let’s see how to use the ALL option within the STDEVP(), as it will take all the column values, including the duplicate values, while computing the standard deviation.

The syntax is given below.

SELECT STDEV(ALL column_name)
FROM tablename;

For example, you have the Product_Sales table shown below.

STDEVP Function in SQL Server with ALL and DISTINCT with Product_Sales Table

The SalePrice column has duplicate values in the Product_Sales table. Let’s apply the STDEVP() function on the SalePrice column to know the variability in the sale price. For that, use the below query.

SELECT STDEVP(ALL SalePrice) AS STDEVPSalePrice 
FROM Product_Sales;
STDEVP Function in SQL Server with ALL Option

When using the ALL option within STDEVP(), it returns a standard deviation of 33187.4434. While calculating the standard of that column, it also included the duplicate values. Just note the return standard deviation value.

Next, use the syntax below to apply the STEDVP() function to only one of the unique values of a column.

SELECT STDEV( DISTINCT column_name)
FROM table_name

Use the below query to compute the standard deviation of all the unique values in the SalePrice column.

SELECT STDEVP(DISTINCT SalePrice) AS STDEVPSalePrice 
FROM Product_Sales;
STDEVP Function in SQL Server with DISTINCT Option

After applying the STDEVP() function with option DISTINCT on the unique values of the SalePrice column, it returns the standard deviation, 339327.5849.

As you can see, the result is slightly different from the previous query, where you used the option ALL.

This is how you can compute the standard deviation of the unique or non-unique values using the STDEVP function in SQL Server.

STDEVP Function in SQL Server with GROUP BY Clause

Using the STDEVP() function with the GROUP BY clause in your query allows you to apply the STDEVP() function on each separate group, or it computes the standard deviation for each group separately.

For example, you have the Sales_By_Region table shown below.

Now, you need to compute the sale amount standard deviation by region, so here, you can use the group by clause in your query to group the sale amount by region and then apply the STDEVP() function on each group.

For example, use the below query for that.

SELECT region, STDEVP(SaleAmount) AS StdByRegion
FROM Sales_By_Region
GROUP BY region;
STDEVP Function in SQL Server with GROUP BY Clause

From the result of the above query, the STDEVP() function is applied to each group and the standard deviation for each group is computed separately. For example, East’s sales amount standard deviation is 299.33, 286.35, and so on for others.

Your query contains the GROUP BY clause, which groups the rows based on the region. Then, the STDEVP() function computes the standard deviation on each region.

This is how to use the STDEVP function in SQL Server to compute the standard deviation based on the group. If you want to know more, check out here STDEVP().

Conclusion

In this SQL Server tutorial, you learned about the STDEVP function in SQL Server with syntax. Then, you computed the sale amount’s standard deviation and learned how the STDEVP() function works with duplicate or unique values in your dataset.

Finally, you applied the STEDVP() function on the table column to compute the sales amount standard deviation by region.

You may like to read: