Hi folks,

I've found a difference between the implementation of the standard deviation function of EDB and a regular spreadsheet.

Here's an example.

Data:

Month   Score
----------------------
1      69.13
2      90.22
3   25.31
4   50.40
5   73.59
6   88.68
7   36.77
8   61.86
9   90.95
10   79.13
11   37.13
12   18.12

If I run the follow query on EDB...

SELECT AVG(score), STDDEV(score) FROM Scores

I get this result..

Avg        stddev
---------  -------
60.11      260125.431719532

However if same data is calculated through a spreadsheet the result would be the following..

Avg           stddev
---------       -------
60.1075    26.0125431719532

EDB is x10000 larger, why?

The explanation could be simple but right now is beyond me...

If you want to test it yourself, heres the SQL.

CREATE TABLE "Scores"
(
"id_score" GUID COLLATE "UNI" DEFAULT CURRENT_GUID,
"month" INT NOT NULL,
"score" DECIMAL(19,2) NOT NULL,
CONSTRAINT "pk_id_score" PRIMARY KEY ("id_score")
)!

INSERT INTO Scores (month, score) VALUES (1, 69.13)!
INSERT INTO Scores (month, score) VALUES (2, 90.22)!
INSERT INTO Scores (month, score) VALUES (3, 25.31)!
INSERT INTO Scores (month, score) VALUES (4, 50.40)!
INSERT INTO Scores (month, score) VALUES (5, 73.59)!
INSERT INTO Scores (month, score) VALUES (6, 88.68)!

INSERT INTO Scores (month, score) VALUES (7, 36.77)!
INSERT INTO Scores (month, score) VALUES (8, 61.86)!
INSERT INTO Scores (month, score) VALUES (9, 90.95)!
INSERT INTO Scores (month, score) VALUES (10, 79.13)!
INSERT INTO Scores (month, score) VALUES (11, 37.13)!
INSERT INTO Scores (month, score) VALUES (12, 18.12)!


SELECT AVG(score), STDDEV(score) FROM Scores

Regards,
Mario

Mario,

It certainly looks like a bug.  I'm looking into it now (the engine has to do some funky calculations due to the way that the aggregates are processed).

Tim Young
Elevate Software
www.elevatesoft.com

Mario,

Okay, this is a weird one, but a typecast from an internal Delphi Currency type to a Double type was causing decimal places to shift.  I'm not exactly sure why yet, but basically the bug was related to the use of a DECIMAL/NUMERIC column data type.

A fix will be in the next build.  Unfortunately, there's no workaround for this.

Tim Young
Elevate Software
www.elevatesoft.com

Thank you Tim!

The workaround for me the time being, would be to divide it by 10,000.

Regards,
Mario

Tim Young [Elevate Software] wrote:

Mario,

Okay, this is a weird one, but a typecast from an internal Delphi Currency type to a Double type was causing decimal places to shift.  I'm not exactly sure why yet, but basically the bug was related to the use of a DECIMAL/NUMERIC column data type.

A fix will be in the next build.  Unfortunately, there's no workaround for this.

Tim Young
Elevate Software
www.elevatesoft.com