MS SQL STDEV retnning in correct values in same query.

SMARGRID 21 Reputation points
2021-09-01T12:33:42.137+00:00

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.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions
{count} votes

2 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 107.2K Reputation points
    2021-09-01T21:56:32.797+00:00

    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.

    0 comments No comments

  2. SMARGRID 21 Reputation points
    2021-09-02T11:16:27.973+00:00

    Thank you Tom for your reply.

    0 comments No comments