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

Question

I am looking for an efficient way to assign a Z-Score (also known as 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 ended up with only 1 row.

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

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 are using Aggregate Functions.

For example, If you are using Min or Max function, then there can be only one Minimum or Maximum value in a table for particular column. Same is the case for AVG function.

Thats why aggregate functions are commonly used with Group By clause.