question

SMARGRID-8719 avatar image
0 Votes"
SMARGRID-8719 asked SMARGRID-8719 answered

MS SQL STDEV retnning in correct values in same query.

Hello All

"Microsoft SQL Server 2014 - 12.0.2000.8 (X64) "

We have query to calculate standard deviation. STDEV(...), for same group of values in one query, its calculating different values.


Reference to below link we have issue in "SQL Server 2014" for calculating STDEVX and STDEVP, does this issue also for STDEV(....) calculation ?


https://support.microsoft.com/en-us/topic/kb3147297-fix-stdevx-p-returns-an-incorrect-result-when-you-calculate-the-standard-deviation-in-sql-server-2014-b764b5d2-a7f3-dbb7-7c03-b6b6134c8e3b

We tried with different version 2016, giving same results.


sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @SMARGRID-8719

Welocme to the microsoft TSQL Q&A forum!

Please refer to Tom's answer, update first and then test. If the problem is not resolved, please feel free to contact us.

Regards
Echo

1 Vote 1 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

You are running the RTM version of SQL 2014. The first thing you need to do is update to a current revision level and retest.

https://docs.microsoft.com/en-US/troubleshoot/sql/general/determine-version-edition-update-level#sql-server-2014

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

As Tom says, you are behind on the service pack level. You should have Service Pack 3, Cumulative Update 4. And given that KB article, that may fix your problem.

Then again, if you see the same thing on SQL 2016, maybe not. STDEV is a floating-point calculation, and if you have a degenerate case with no variation, there may be flutter that results in an unexpected value - but which after all is close to the actual answer.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SMARGRID-8719 avatar image
0 Votes"
SMARGRID-8719 answered

Thank you Tom for your reply.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.