MySQL Why does it automatically sum when using an aggregate function
I am trying to write a MySQL query that calculates the standard deviation of a numeric column without using STD (). My working query looks like this:
SELECT sqrt(power((amount-avg(amount)),2)/count(amount))
FROM Orders;
Please just treat "quantity" as the column name and "Orders" as the table name. My question is, why is there an automatic sum of all power results ((sum-aug (sum)), 2) without using the sum function?
Thank!
source to share
You are using the count () function, which is an aggregate function, this way you get all GROUPed rows. In your query, the sum is not calculated as a sum, it only uses SOME of the row values (it is not defined which row values are used) since MySQL allows you to use ungrouped columns in combination with grouped ones.
More details here: https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
If you only want to implement it yourself, you can try executing the query like this:
SELECT @avg:=(SELECT avg(amount) FROM Orders), @cnt:=(SELECT count(*) FROM Orders),
SQRT(SUM(POWER(amount-@avg,2)/@cnt)) as std
FROM Orders
This will cause the two quick queries to get the average and only count once (since they are all the same for every row) and then it will sum your formula and return sqrt from it as std.
source to share