StDev() function returns Null when table contains only one row

Question

I am trying to use the StDev function and am getting blank results. I am using it as...

SELECT StDev(fldMean) FROM myTable

Where fldMean contains a value of 2.3 and should evaluate to 0 but instead I am simply getting an empty result. I can't seem to understand how expressions are to be used in the function, Microsoft's manual really didn't help.

what is the datatype of fldMean? your query is correct, either there is a problem with the type or StDev doesn't work if there's only 1 row — serakfalcon, Jan 05 '14 at 18:02
That is the problem, narrowed my data set down to the point where I only had one row. — codingManiac, Jan 05 '14 at 18:08

score 12 · Accepted Answer · answered Jan 05 '14 at 18:10

SELECT StDev(fldMean) FROM myTable

will return Null if [myTable] has only one row because the Standard Deviation cannot be computed from a single observation. You will need at least two rows in that table before you can receive a meaningful result. If you want to force the Null value to zero you can use

SELECT Nz(StDev(fldMean), 0) FROM myTable

StDev() function returns Null when table contains only one row

1 Answers1

Linked