Name
STDDEV()
Synopsis
STDDEV(expression
)
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
are found. Here is an example:
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()
.
Get MySQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.