MySQL Why automatically sum when using aggregate function

Question

I'm trying to write a MySQL query that calculate Standard Deviation of a numeric column without using STD(). My working query is as below:

SELECT sqrt(power((amount-avg(amount)),2)/count(amount))
FROM Orders;

Please just treat 'amount' as the column name and 'Orders' as the table name. My question is why there is an automatically sum of all results of power((amount-avg(amount)),2) without using a sum function?

Thanks!

Answer 1

You are using count() function, which is an aggregate function, thus you get all rows GROUPed. In your query, amount is not calculated as a sum, it uses just SOME row's value (it is not determined which row's values is used), since MySQL allows for using non-grouped columns combined with grouped ones.

Read more here: https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

If you wish to implement it only yourself, you can try 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 do two fast queries getting average and count only once (since these are still the same for every row) and then it will sum up your formula and return sqrt of it as std.

Answer 2

Your amount column do not calculate sum. Its the SOME row's value of your table.