[bens1901]

Hi all.

I'm trying to calculate the Standard Deviation for some data in my db. Everything executes, but I get funny results. The MySQL results are different from the results that Microsoft Excel give me. When I performed the calculations by hand I got results that matched Excel...so this makes me wonder why MySQL is giving me incorrect numbers. The Average (mean) functions of MySQL and Excel give matching results, but the STD functions differ. (note: I get the same results for "STD" and "STDDEV"..thought I'd try both, even though some documents seemed to make them sound the same)

Any help would be greatly appreciated.

Thanks.

-Ben


Here is the table with the data:

CREATE TABLE `stats_test` (
`id` int(11) NOT NULL auto_increment,
`x_value` int(11) default NULL,
`y_value` int(11) default NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=9 ;

INSERT INTO `stats_test` (`id`, `x_value`, `y_value`) VALUES (1, 5, 2);
INSERT INTO `stats_test` (`id`, `x_value`, `y_value`) VALUES (2, 1, 4);
INSERT INTO `stats_test` (`id`, `x_value`, `y_value`) VALUES (3, 4, 6);
INSERT INTO `stats_test` (`id`, `x_value`, `y_value`) VALUES (4, 3, 5);
INSERT INTO `stats_test` (`id`, `x_value`, `y_value`) VALUES (5, 5, 7);
INSERT INTO `stats_test` (`id`, `x_value`, `y_value`) VALUES (6, 4, 3);
INSERT INTO `stats_test` (`id`, `x_value`, `y_value`) VALUES (7, 5, 1);
INSERT INTO `stats_test` (`id`, `x_value`, `y_value`) VALUES (8, 7, 9);

Here's the SQL query:
select AVG(x_value) x_avg, STD(x_value) x_std, AVG(y_value) y_avg, STD(y_value) y_std from stats_test

Here's what I get from MySQL:
x avg. 4.25
y avg. 4.625
x std. dev. 1.6394
y std. dev. 2.4969

Here's what I get from Excel:
x avg. 4.25
y avg. 4.625
x std. dev. 1.75
y std. dev. 2.67