CODEWITHSUNDEEP
t-sql
Jack
Jack

Reputation: 61

Calculating Zscore for multiple rows

My table store measurement taken on several visits of a person. i want to calculate the zscore for each person.i notice my answer is not correct because is calculating for each row only.I want for each ID, the zscore on all four visits. below is what i have so far.

 `select ID,VNo1,VNo2,VNo3,VNo4
  .W1,AVG(W1) AS Mean , STDEVP(W1) AS StandardDeviation
    , STDEVP(W1) * STDEVP(W1) AS Zscore,

from dbo.measurement 
GROUP BY ID,VNo1,VNo2,VNo3,VNo4

`

Upvotes: 0

Views: 92

Answers (1)

EzLo
EzLo

Reputation: 14189

I'm not sure what values do the VNoX columns hold, but if you have 1 row per visit then you just need to remove columns from your GROUP BY.

select 
    ID,
    AVG(W1) AS Mean ,
    STDEVP(W1) AS StandardDeviation,
    STDEVP(W1) * STDEVP(W1) AS Zscore,
from 
    dbo.measurement 
GROUP BY 
    ID

Upvotes: 1

Related Questions