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.

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;`

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.

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;
```

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;
```

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.

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;
```

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:

After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.