BUG? STD function returning 0 when used with GROUP BY field from related table
am 10.07.2002 14:58:54 von Judy SimonHow-To-Repeat:
In the below examples you can see my progression until the ERROR situation
is reached [NOTE: The MySQL server is running version 3.22.23b].
1. create table:
create table stdtest (
id int(8) auto_increment not null primary key,
name varchar(16) not null,
gender int(4),
score int(4),
accuracy float(6,2)
)
2. populate it:
insert into stdtest (name,gender,score,accuracy) values
('joe',1,80,84.3),('alan',1,96,66.2),
('jane',2,75,94.7),('alice',2,98,91.5),
('harry',1,99,74.3),('sally',2,88,86.9),
('mark',1,85,88.4),('peter',1,92,97.3),
('judy',2,100,99.0),('vivi',2,86,57.2)
3. using avg and std WITHOUT group by clause
SELECT count(name) as N, avg(score) as ScoreAvg, std(score) as ScoreStd,
avg(accuracy) as AccAvg, std(accuracy) as AccStd FROM stdtest
--------------------------------------------------
N ScoreAvg ScoreStd AccAvg AccStd
10 89.9000 8.0926 83.980001 13.151639
4. using avg and std WITH group by clause ( field in SAME table)
SELECT gender, count(name) as N, avg(score) as ScoreAvg, std(score) as
ScoreStd, avg(accuracy) as AccAvg, std(accuracy) as AccStd FROM stdtest
Group by gender
----------------------------------------------
gender N ScoreAvg ScoreStd AccAvg AccStd
1 5 90.4000 7.0029 82.100002 10.855599
2 5 89.4000 9.0244 85.860000 14.866687
5. create related table:
create table stdeyes (
id int(8) auto_increment not null primary key,
color varchar(16) not null
)
6. populate it:
insert into stdeyes (color) values
('brown'),('hazel'),('blue'),('brown'),('brown'),
('brown'),('blue'),('brown'),('hazel'),('hazel')
7. using avg and std WITH group by clause ( field in RELATED table)
SELECT color, count(name) as N, avg(score) as ScoreAvg, std(score) as
ScoreStd, avg(accuracy) as AccAvg, std(accuracy) as AccStd FROM
stdtest,stdeyes where stdtest.id=stdeyes.id Group by color
--------------------------------------------------------
color N ScoreAvg ScoreStd AccAvg AccStd
blue 2 80.0000 0.0000 91.549999 0.000000
brown 5 91.4000 0.0000 86.860002 0.000000
hazel 3 94.0000 0.0000 74.133333 0.000000
HERE YOU CAN SEE THE ERROR!!!!
what happened to the STD values???
8. I even tried FROM stdtest LEFT JOIN stdeyes on stdtest.id=stdeyes.id and
got the same error!
9. Is this a known bug that is fixed in a more recent version?
Is there a workaround [using these example tables] via a temporary table
that joins all the fields into one table?
How would I create it?
I really need to be able to perform this function and time is becoming of
the essence - Please help!
Thank You!
Judy Simon
Webmaster & DB Systems Engineer
NeuroTrax Israel LTD, Shilat, Israel Tel: +972-8-976-3067 Fax:
+972-8-976-3068
------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail bugs-thread12172@lists.mysql.com
To unsubscribe, e-mail