SQL STDEVP Function

STDEVP returns the standard deviation for the population of the given values.

STDEVP ignores NULL values.

Use STDEVP if the given values represent the entire population.

Use STDEV if the given values represent a sample.

Example

#

This example returns the standard deviation for the population of all sales.

SELECT STDEVP(TotalAmount) AS 'Standard Deviation'
  FROM [Order]
Result:  1 record
Standard Deviation
1989.41443687422

Syntax

Syntax of the STDEVP function.

STDEVP(value)

value -- a number or numeric column.

More Examples

STDEVP with GROUP BY

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax
Problem: List suppliers with the standard deviation for the population of their product prices.
SELECT S.CompanyName, 
       STDEVP(UnitPrice) AS 'Price Standard Deviation'
  FROM Product P
  JOIN Supplier S ON S.Id = P.SupplierId
 GROUP BY S.CompanyName
Result:  29 records
CompanyName Price Standard Deviation
Aux joyeux ecclésiastiques 122.75
Bigfoot Breweries 1.88561808316412
Cooperativa de Quesos 'Las Cabras' 8.5
Escargots Nouveaux 0
Exotic Liquids 4.02768199119819

Note: A value of 0 is returned when a supplier has fewer than 2 products.


You may also like



Guides


vsn 3.1