intro
Let’s learn how to use SQL standard deviation functions to calculate the standard deviation of numeric columns in your database.
Standard deviation is a measure of the amount of variation or dispersion in a list of values. It indicates how spread out the numbers are in a dataset, with a higher standard deviation showing greater variability. This operation is widely used in statistical analysis and is so common that SQL provides functions to compute it. Discover the SQL standard deviation functions and learn how to apply them to your data!
What Are the SQL Standard Deviation Functions?
Standard SQL provides two functions for calculating standard deviation:
See the difference between sample and population standard deviation.
The STDDEV_POP() and STDDEV_SAMP() functions are supported by databases like MySQL, PostgreSQL, and Oracle. SQL Server uses STDEV() for sample standard deviation and STDEVP() for population standard deviation instead.
Some databases also offer STDDEV(), but its meaning varies. For example:
Note: All the above functions are aggregate functions, which means they operate on multiple rows and return a single result. You can only use them in SELECT, HAVING, ORDER BY clauses, or with GROUP BY when applied to grouped data.
Summary of Standard Deviation SQL Functions
| Database | Sample Standard Deviation | Population Standard Deviation | Notes |
|---|---|---|---|
| MySQL | STDDEV_SAMP() | STDDEV_POP()/STDDEV()/STD() | STDDEV() and STD() are synonyms for STDDEV_POP() |
| PostgreSQL | STDDEV_SAMP() / STDDEV() | STDDEV_POP() | STDDEV() is a synonym for STDDEV_SAMP() |
| SQL Server | STDEV() | STDEVP() | Does not support STDDEV_POP() or STDDEV_SAMP() |
| Oracle | STDDEV_SAMP() / STDDEV() | STDDEV_POP() | STDDEV() returns 0 instead of NULL on 1-row scenarios |
How to Calculate the Standard Deviation in SQL
Now that you understand SQL standard deviation functions, you are ready to learn how to use them in queries.
Standard SQL (MySQL, PostgreSQL, Oracle)
Here are the syntax structures and explanations for standard SQL standard deviation functions:
Notes:
SQL Server
The SQL Server standard deviation functions are:
The syntax of the two aggregate functions is:
1
STDEV|STDEVP([ALL|DISTINCT] expression)
Where:
Notes:
SQL Standard Deviation: Complete Example
To better understand how standard deviation calculations work in SQL, let’s go through a complete example.
Note 1: The queries below will be executed in DbVisualizer, a visual database client that makes it easier to interpret the results.
Note 2: The sample queries will be written in MySQL and follow standard SQL syntax.
Time for a standard deviation SQL example!
Step #1: Data Presentation
Suppose you have a sales_data table that stores the number of items sold by your e-commerce store on a daily basis. It contains the following sample data:
As you can see, the sales_amount column can be NULL, which could indicate either that there were no sales on that day or that the number of sales is unknown. After all, NULLs are not the same.
See how to apply the standard deviation functions on the sales_amount field!
Step #2: Execute STDDEV_SAMP()
You can calculate the sample standard deviation of the sales_amount field using STDDEV_SAMP() as below:
1
SELECT STDDEV_SAMP(sales_amount)
2
FROM sales_data;
The result will be:
As you can see, STDDEV_SAMP() automatically ignores NULL values, as expected. Verify that on the Standard Deviation Calculator page:
If you want to treat NULL values as 0s instead, you can modify the query like this:
1
SELECT STDDEV_SAMP(COALESCE(sales_amount, 0))
2
FROM sales_data;
The SQL COALESCE function will replace NULL values with 0 before calculating the standard deviation, leading to a different result:
As you can verify in the image below:
Great! Mission complete.
Step #3: Execute STDDEV_SAMP()
Now, assume you want to calculate the population standard deviation. This time, you have to use the STDDEV_POP() function:
1
SELECT STDDEV_POP(sales_amount)
2
FROM sales_data;
The result will be:
Verify the result with the online standard deviation calculation tool:
Fantastic! You now know how to apply standard deviation in SQL.
Conclusion
In this guide, you learned about the standard deviation SQL functions and how to use them to calculate standard deviation in SQL. As shown here, STDDEV_SAMP() and STDDEV_POP() are the two standard deviation functions in ANSI SQL. On the other hand, SQL Server offers STDEV() and STDEVP() to achieve similar results.
Working with numerical operations becomes much easier with a database client that provides a complete visual data representation and supports all functions from the most popular databases worldwide—just like DbVisualizer.
FAQ
How does the SQL STDEV function handle NULL values?
The SQL STDEV function ignores NULL values when calculating standard deviation. It only considers non-null numeric values in the dataset. If all the values are NULL, the result will be NULL.
What is the difference between the STDEVP and STDEV SQL Server functions?
The main difference between the STDEVP() and STDEV() functions in SQL Server is that STDEVP() calculates the population standard deviation, while STDEV() calculates the sample standard deviation. Thus:
In other words, STDEVP() divides by the total number of values, while STDEV() divides by one less than the total number of values.
What is the difference between STD vs STDDEV vs STDDEV_POP vs STDDEV_SAMP in MySQL?
| Function | Description | Population or Sample |
|---|---|---|
STD() | Calculates standard deviation (synonym for STDDEV_POP) | Population |
STDDEV() | Synonym for STDDEV_POP | Population |
STDDEV_POP() | Calculates the population standard deviation | Population |
STDDEV_SAMP() | Calculates the sample standard deviation | Sample |
In MySQL, STD and STDDEV are synonyms for STDDEV_POP, and STDDEV_SAMP is used for sample standard deviation.
What databases support an SQL standard deviation function?
Many popular databases support SQL standard deviation functions, including:
Why use a database client with numerical operations?
Working with numerical operations like standard deviation is much easier with a visual database client like DbVisualizer. It allows you to explore input data and view results visually, helping you better understand the output. Then, features like query visualization and SQL formatting make it simpler to work with and initiate aggregate functions, enhancing your overall experience and efficiency in analyzing data. Grab a 21-day free trial today!
