Table of Contents for

MySQL in a Nutshell, 2nd Edition

This function returns the sample 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 are found. It was added in version 5.0.3 of MySQL for compliance with SQL standards. Here is an example:

SELECT CONCAT(name_first, SPACE(1), name_last) AS rep_name,
AVG(sale_amount) AS avg_sale_per_ticket, 
STDDEV_POP(sale_amount) AS population_std_dev,
STDDEV_SAMP(sale_amount) AS sample_std_dev
FROM sales 
JOIN sales_reps USING(sales_rep_id)
GROUP BY sales_rep_id;

This SQL statement uses several aggregate functions: AVG() to determine the average sale for each sales rep; STDDEV_POP() to determine how much each sale made by each sales rep tends to vary from each rep’s average sale; and STDDEV_SAMP() to determine the standard deviation from the average based on a sample of the data.