STDEV Function in SQL Server

In this SQL Server tutorial, you will learn about the STDEV Function in SQL Server, where you will learn about the standard deviation and how to compute it using the STDEV() function.

If you are a data analyst or data science enthusiast, this function is very helpful, as it lets you quickly retrieve the given data’s standard deviation. So here, you will understand the syntax of the STDEV() function.

Also, you will learn how to use this function when your dataset contains duplicate values; as you know, duplicate values or unwanted data in your data create noise, and your analysis doesn’t give good results as expected.

You will apply the STDEV function in SQL Server to the real-world table data.

STDEV Function in SQL Server

The STDEV function in SQL Server stands for standard deviation, so the STDEV() function calculates and returns the standard deviation of the given set of values (sample data). The standard deviation measures the amount of a set of data dispersion to its mean.

In simple words, how far is each value in a set of data from the mean? A lower standard deviation means the value is close to the mean, and a higher standard deviation means the value is far from the mean.

This function is very useful in statistical analysis, which allows data professionals to understand the variability in the data sets.

The syntax is given below.

STDEV(ALL | DISTINCT expression)

Where,

  • STDEV(): The function computes the standard deviation of the given data set.
  • expression: The expression can be the columns, set of values, or any numeric set of data. But it can’t be bit type data type; remember, as an expression, you can’t use the aggregate function or subqueries.
  • ALL | DISTINCT: When you use the STDEV function in SQL Server with different options, such as ALL or DISTINCT, it computes the standard deviation differently.
    • STDEV(ALL expression): When you use the STDEV() function with the ALL option, it applies the STDEV() function on all the values of the given expression (columns values), including the duplicated values in the columns of the table.
    • STDEV(DISTINCT expression): This one only applies the STDEV() function on the unique values of the columns while banning the duplicate ones.

The return type of the STDEV function in SQL Server is a float, which means after calculating the standard deviation of the given set of values, it returns the number, and the data type of that number is always float type.

With an example, let’s see how the STDEV() function works. Suppose you have a table ProductSales with columns ProductName and Sales, as shown below.

STDEV Function in SQL Server ProductSales Table

Now, you need to find the standard deviation of all the sales values in the table ProductSales. For that, use the query below.

SELECT STDEV(Sales) StdevSales FROM ProductSales;
STDEV Function in SQL Server Applying on ProductSales Table

From the output of the above query, you can see the sales’ standard deviation in the Sales column is 146.68.

This is how to use the STDEV function in SQL Server to compute the standard deviation of the values in the table’s column.

STDEV Function in SQL Server with ALL and DISTINCT

When the STDEV() function is used with options ALL and DISTINCT, it returns a different standard deviation.

First, use the ALL option within the STDEV(), as it will take all the values of the column, including the duplicate values.

The syntax is given below.

SELECT STDEV(ALL column_name)
FROM table_name;

For example, you have a Product_Sales table, which is shown below.

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

As you can see, the SalePrice column contains the duplicate sale price (values such as 59999.99). To compute the standard deviation of all the values in that column with the ALL option within the STDEV() function. So use the below query.

SELECT STDEV(ALL SalePrice) AS STDEVSalePrice 
FROM Product_Sales;
STDEV Function in SQL Server with ALL option on Product_Sales Table

When you have used the ALL option with the STDEV() function, it returns the result as a 349819.00 value, the standard deviation; the STDEV() function also considers the duplicate values while computing the standard deviation. Here, the STDEV() function is applied to all the values of the column SalePrice.

Now, use the syntax below to apply the STDEV() function only on the unique value of the SalePrice column to compute the standard deviation.

SELECT STDEV( DISTINCT column_name)
FROM table_name;

Again, compute the standard deviation of all the values in the SalePrice column using the query below.

SELECT STDEV(DISTINCT SalePrice) AS STDEVSalePrice 
FROM Product_Sales;
STDEV Function in SQL Server with DISTINCT option on Product_Sales Table

The output shows that the standard deviation is different this time because you are applying the STDEV() functions on unique values of the columns using the DISTINCT option. As a result, it returns the value 359911.25.

This is how you can use the ALL or DISTINCT option with the STDEV function in SQL Server to calculate the standard deviation of the column values.

STDEV Function in SQL Server using GROUP BY clause

You can use the GROUP BY clause to group the rows based on your query’s specified column or criteria. Then, you can compute the standard deviation for each group separately using the GROUP BY clause.

For example, you have a Sales table with columns sale_id, product_id, sale_amount, and sale_date, as shown below.

STDEV Function in SQL Server using GROUP BY clause Sales Table

Using the data in the above table, you must compute the standard deviation of each product’s sale amounts separately. For that, use the below query.

SELECT product_id, STDEV(sale_amount) AS StdDevSale
FROM Sales
GROUP BY product_id;
STDEV Function in SQL Server using GROUP BY clause

The output of the above query shows the standard deviation of sales for each product separately. For example, a product with ID P100 has a standard deviation of 18.25, P101 has 13.22, etc.

Also, the standard deviation for some of the product IDs is NULL because it returns the null when you pass the NULL values to the STDEV() function.

But here, you understand from the above example that you can apply the STEDEV() function on a group of data or group-wise using the GROUP BY clause.

I hope you understand how to use the STDEV function in SQL Server to compute the standard deviation of the given set of data or the table column values.

Conclusion

In this SQL Server tutorial, you learned how to calculate the standard deviation of the table columns values using the STDEV() function in SQL Server. You also learned where this could be useful, used this function on the table column values, and computed the standard deviation using the GROUP BY clause to apply the STDEV() function group-wise.

You may like to read: