Calculating Z-Score for each row in MySQL? (just)

Question

I am looking for an effective way to assign a Z-Score (also known as a standard score) for each row in a MySQL table.

Z = Z-Score X = Actual value μ = Mean value σ = Standard Deviation 

I tried:

 SELECT pTime,(M1-AVG(M1))/STD(M1), (M2-AVG(M2))/STD(M2), (M3-AVG(M3))/STD(M3), (M4-AVG(M4))/STD(M4) FROM mergebuys; 

but in the end there was only 1 row.

It seems really inefficient to use a subquery when it only needs to be calculated.

Answer 1

 SELECT mergebuys.pTime, (mergebuys.M1 - aggregates.AVGM1) / aggregates.STDM1 AS z1, (mergebuys.M2 - aggregates.AVGM2) / aggregates.STDM2 AS z2, (mergebuys.M3 - aggregates.AVGM3) / aggregates.STDM3 AS z3, (mergebuys.M4 - aggregates.AVGM4) / aggregates.STDM4 AS z4 FROM mergebuys CROSS JOIN ( SELECT AVG(M1) AS AVGM1, STD(M1) AS STDM1, AVG(M2) AS AVGM2, STD(M2) AS STDM2, AVG(M3) AS AVGM3, STD(M3) AS STDM3, AVG(M4) AS AVGM4, STD(M4) AS STDM4 FROM mergebuys ) AS aggregates 

Answer 2

Because you use aggregated functions.

For example, if you use the Min or Max function, then there can be only one minimum or maximum value in a table for a certain column. The same goes for the AVG function.

This is why aggregate functions are commonly used with a Group By clause.