How to Calculate Standard Deviation in PostgreSQL

Often in PostgreSQL you may want to calculate the standard deviation of values in a particular column.

You can use the stddev() function with the following methods to do so:

Method 1: Calculate Standard Deviation of One Column

SELECT stddev(points)
FROM athletes;

This particular example will calculate the standard deviation of values in the points column of the athletes table.

Method 2: Calculate Standard Deviation by Group

SELECT team,
       stddev(points)
FROM athletes
GROUP BY team;

This particular example will calculate the standard deviation of values in the points column, grouped by the values in the team column of the athletes table.

Note: The stddev() function is an alias for the stddev_samp() function, which is used to calculate the sample standard deviation.

If your dataset represents the entire population of interest then you could use the stddev_pop() function instead, which calculates the population standard deviation.

The following example shows how to use each of these methods in practice.

Example: How to Calculate Standard Deviation in PostgreSQL

Suppose that we create the following table named athletes that contains information about points scored by various basketball players:

-- create table named 'athletes'
CREATE TABLE athletes (
  id INTEGER,
  team TEXT,
  points INTEGER
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', 22);
INSERT INTO athletes VALUES (0002, 'Mavs', 30);
INSERT INTO athletes VALUES (0003, 'Mavs', 12);
INSERT INTO athletes VALUES (0004, 'Mavs', 9);
INSERT INTO athletes VALUES (0005, 'Mavs', 14);
INSERT INTO athletes VALUES (0006, 'Heat', 16);
INSERT INTO athletes VALUES (0007, 'Heat', 40);
INSERT INTO athletes VALUES (0008, 'Heat', 18);
INSERT INTO athletes VALUES (0009, 'Heat', 16);
INSERT INTO athletes VALUES (0010, 'Heat', 33);

-- view all rows in table
SELECT * FROM athletes;

 id | team | points 
----+------+--------
  1 | Mavs |     22
  2 | Mavs |     30
  3 | Mavs |     12
  4 | Mavs |      9
  5 | Mavs |     14
  6 | Heat |     16
  7 | Heat |     40
  8 | Heat |     18
  9 | Heat |     16
 10 | Heat |     33

We can use the following syntax to calculate the standard deviation of values in the points column:

SELECT stddev(points)
FROM athletes;

       stddev        
---------------------
 10.1105005920687341

From the output we can see that the standard deviation of values in the points column is 10.1105.

We could also use the following syntax to calculate the standard deviation of values in the points column grouped by the values in the team column:

SELECT team,
       stddev(points)
FROM athletes
GROUP BY team;

 team |       stddev        
------+---------------------
 Heat | 11.1713920350151529
 Mavs |  8.5322916030806167

From the output we can see:

• The standard deviation of points for the Heat is 11.1714.
• The standard deviation of points for the Heat is 8.5323.

Feel free to use similar syntax to calculate the standard deviation of values in a column in your own table in PostgreSQL.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Select Top N Percent of Records
PostgreSQL: How to Calculate a Moving Average
PostgreSQL: How to Calculate Average Across Multiple Columns