RE: Invalid syntax with STD() function when more than one field is used in select query
Oops, the version numbers were 4.1.18-nt and 4.1.19-standard. I have tried it now on the latest 4.1.20 version and still have the same problem. Does anyone have any ideas? Is this a bug? Cheers, Bill > -Original Message- > From: William Bronsema > Sent: Thursday, July 20, 2006 10:18 AM > To: mysql@lists.mysql.com > Subject: Invalid syntax with STD() function when more than one field is > used in select query > > Hello, > > I am encountering a strange issue when using the STD function. On my > local development machine (MYSQL version 4.18-nt) I can run the following > basic SELECT query with no problems: > > SELECT STD(`LAPSETIME`),UKEY FROM 4b3f91f64a19529a84dff4982c8a6bc5 GROUP > BY UKEY > > When I test this query on my hosted production machine (MYSQL version > 4.19-standard) that query results in an invalid syntax error: > > "#1064 - You have an error in your SQL syntax; check the manual that > corresponds to your MySQL server version for the right syntax to use near > '( `LAPSETIME` ) , UKEY FROM 4b3f91f64a19529a84dff4982c8a6bc5 GROUP BY > UKEY > LIM' at line 1" > > The query will work if I remove the UKEY field in the select: > > SELECT STD(`LAPSETIME`) FROM 4b3f91f64a19529a84dff4982c8a6bc5 > > Any ideas? > > Cheers, > Bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Invalid syntax with STD() function when more than one field is used in select query
Hello, I am encountering a strange issue when using the STD function. On my local development machine (MYSQL version 4.18-nt) I can run the following basic SELECT query with no problems: SELECT STD(`LAPSETIME`),UKEY FROM 4b3f91f64a19529a84dff4982c8a6bc5 GROUP BY UKEY When I test this query on my hosted production machine (MYSQL version 4.19-standard) that query results in an invalid syntax error: "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( `LAPSETIME` ) , UKEY FROM 4b3f91f64a19529a84dff4982c8a6bc5 GROUP BY UKEY LIM' at line 1" The query will work if I remove the UKEY field in the select: SELECT STD(`LAPSETIME`) FROM 4b3f91f64a19529a84dff4982c8a6bc5 Any ideas? Cheers, Bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Avg and Std function - null values
Hi, (B (BI have a table with the sales of each product by month (in the format (Bmm). (BFor example: (table name: ventas) (B (Bsale_date prod_idsales (B200301 A20 (B200302 A16 (B200303 A18 (B200301 B12 (B200302 B 3 (B200304 B10 (B (BI would like to have the average sales per month of each product. (BIf I use: (B"SELECT prod_id, avg(sales) as average (B FROM ventas (B GROUP by prod_id" (B (Bthen I would get the average sales from Jan 2003 to Mar 2003 for product A (B(18.0) and the average sales for product B (8.33). (BHowever, as you would have probably noticed, my table covers the sales (Bperiod Jan 2003 to Apr 2003. I would like to get the average sales for the (Bperiod Jan-Apr 2003 for each product: A (13.5) and B (6.25). (B (BI know that if I add the missing lines with value 0 the problem would be (Bsolved, but I would like to know an efficient way to add those lines (Bwithout having to check the table for missing values with a script (I also (Bneed to calculate the standard deviation). (B (BThanks in advance for your help. (B (Bkelwin (B (B_ $BM'C#$H(B24$B;~4V%[%C%H%i%$%s!V(BMSN $B%a%C%;%s%8%c!http://messenger.msn.co.jp (B (B (B-- (BMySQL General Mailing List (BFor list archives: http://lists.mysql.com/mysql (BTo unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: STD()
[EMAIL PROTECTED] wrote: > Is this correct?...how MySQL calculate this?... Yes. Std. Dev = Math.sqrt(sum((val[i]-avg)**2) / count(i)); Verified with a trivial program. > > mysql> select * from temp; > +--+ > | cal | > +--+ > | 00029.98 | > | 00029.95 | > | 00029.89 | > | 00029.84 | > | 00029.78 | > | 00029.81 | > | 00029.84 | > | 00029.28 | > +--+ > 8 rows in set (0.00 sec) > > mysql> > > mysql> select std(cal) from temp; > +--+ > | std(cal) | > +--+ > | 0.205240 | > +--+ > 1 row in set (0.06 sec) > > mysql> > > > > > R.B.Roa > Traffic Management Engineer > PhilCom Corporation > Tel.No. (088) 858-1028 > Mobile No. (0919) 30856267 > > > - > 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 > - 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
STD()
Everyone, Is this correct?...how MySQL calculate this?... mysql> select * from temp; +--+ | cal | +--+ | 00029.98 | | 00029.95 | | 00029.89 | | 00029.84 | | 00029.78 | | 00029.81 | | 00029.84 | | 00029.28 | +--+ 8 rows in set (0.00 sec) mysql> mysql> select std(cal) from temp; +------+ | std(cal) | +--+ | 0.205240 | +--+ 1 row in set (0.06 sec) mysql> R.B.Roa Traffic Management Engineer PhilCom Corporation Tel.No. (088) 858-1028 Mobile No. (0919) 30856267 - 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
STD function
Hi I'm doing a simple query using the function STD but I'm getting zeros as answer. I know that zero is a perfectly good answer for standart deviation but in this case I should get some positive values :-) Is there something wrong with this function ? My sql string is the folowing and I'm using version 3.22.32. SELECT ROUND(ESTACOES.LAT_I,1) AS LAT, ROUND(ESTACOES.LON_I,1) AS LON, ROUND(AVG(ESTACOES.PROF_I),1) AS PRF, ROUND(AVG(ESTACOES_REC.RECRUTAS),1) AS REC, STD(ESTACOES_REC.RECRUTAS) AS STDEV FROM ESTACOES, ESTACOES_REC WHERE ESTACOES.CRUZEIRO=ESTACOES_REC.CRUZEIRO AND ESTACOES.ESTACAO=ESTACOES_REC.ESTACAO AND ESTACOES.CRUZEIRO!='2420982' GROUP BY LAT,LON Thanks EJ - 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