Hello all, I am trying to build a relatively complex query to get the average and standard deviation of various testing variables which ultimately will be filtered/grouped by age and other parameters.
Item #1 shows the original query and output: select count(Test1000.OID) as N, avg(Accuracy) as AccAvg, std(Accuracy) as AccStd, avg(AvgRespT) as RTAvg, std(AvgRespT) as RTStd , from Test1000,ROrders,ptProt,protocols,RPtDemog WHERE ROrders.OID=Test1000.OID AND ptProt.protID=protocols.protID AND ptProt.PID=ROrders.PID AND ptProt.PID=RPtDemog.PID AND Language=1 AND Trusted='Yes' ----------------------------- N AccAvg AccStd RTAvg RTStd 402 92.9680 9.7709 445.2773 121.2830 Item #2 is the same query but has an added Age Bracket field to be grouped by: [if its hard to read, age is computed as the (date the person was enrolled in a clinical trial - their DOB)/365 and this Age is separated into brackets - This part works fine...] select count(Test1000.OID) as N, if (round((to_days(dateStart)-to_days(DOB))/365)<30,'<30', if (round((to_days(dateStart)-to_days(DOB))/365)>=30 AND round((to_days(dateStart)-to_days(DOB))/365)<50,'30-50', if (round((to_days(dateStart)-to_days(DOB))/365)>=50 AND round((to_days(dateStart)-to_days(DOB))/365)<70,'50-70','70+'))) as Bracket, avg(Accuracy) as AccAvg, std(Accuracy) as AccStd, avg(AvgRespT) as RTAvg, std(AvgRespT) as RTStd from Test1000,ROrders,ptProt,protocols,RPtDemog WHERE ROrders.OID=Test1000.OID AND ptProt.protID=protocols.protID AND ptProt.PID=ROrders.PID AND ptProt.PID=RPtDemog.PID AND Language=1 AND Trusted='Yes' Group By Bracket ------------------------------- N Bracket AccAvg AccStd RTAvg RTStd 121 30-50 96.2500 0.0000 406.3667 0.0000 59 50-70 94.2542 0.0000 435.7627 0.0000 129 70+ 88.0833 0.0000 533.5278 0.0000 93 <30 93.6250 0.0000 396.4091 0.0000 MY QUESTION:What happened to the standard deviation columns between the first and second query? I would appreciate any insights anyone could offer! Thanks in advance. 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 <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
