How to get a Sample Standard Deviation values that match condition?

Question

I am trying to get the standard deviation of a subset of data. I have two sheets

Name Value
a    1
a    2
a    1
b    5
b    5

In a separate sheet I have:

Name StdDev
a   
b    

GOAL: I am trying to get:

Name StdDev
a    .57
b    0

I tried:

=query('Sheet1'!A1:B999,"select STDEV(B) where A='"&A1&"'")

Error:

Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "(" "( "" at line 1, column 13. Was expecting one of: "where" ... "group" ... "pivot" ... "order" ... "skipping" ... "limit" ... "offset" ... "label" ... "format" ... "options" ... "," ... "" ... "+" ... "-" ... "/" ... "%" ... "" ... "/" ... "%" ... "+" ... "-" ...

Answer 1

• first of all, there is no STDEV(Col) parameter in Google Sheets' QUERY so you need to create it from:

=SQRT(SUM(ARRAYFORMULA((QUERY({Sheet1!$A$2:$B}, "select Col2 where Col1='"&D2&"'")-
 AVERAGE(QUERY({Sheet1!$A$2:$B}, "where Col1='"&D2&"'")))^2))/
 (COUNTIF(Sheet1!$A$2:$A, "="&D2)-1))

Answer 2

shorter alternative:

• cell D2: =UNIQUE(Sheet1!A2:A)
• cell E2: =STDEV(FILTER(Sheet1!B:B, Sheet1!A:A=D2))

• cell E3: =STDEV(FILTER(Sheet1!B:B, Sheet1!A:A=D3))