STDDEV( )

This MySQL function returns the population standard deviation of the given column.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

STDDEV(expression)

Explanation

This function returns the population standard deviation of the given column. It's normally used in conjunction with a GROUP BY clause specifying a unique column, so that values are compared for each unique item separately. It returns NULL if no matching rows were found.

Examples

SELECT CONCAT(name_first, SPACE(1), name_last) AS rep_name,
SUM(sale_amount) AS total_sales,
COUNT(sale_amount) AS total_tickets,
AVG(sale_amount) AS avg_sale_per_ticket, 
STDDEV(sale_amount) AS standard_deviation
FROM sales 
JOIN sales_reps USING(sales_rep_id)
GROUP BY sales_rep_id;

This statement employs several aggregate functions. We use SUM( ) to get the total sales for each sales rep, COUNT( ) to retrieve the number of orders for the each, AVG( ) to determine the average sale, and STDDEV( ) to find out how much each sale made by each sales rep tends to vary from each one's average sale. Incidentally, statistical functions return several decimal places. To return only two decimal places, you can wrap each function in FORMAT( ).