List:General Discussion« Previous MessageNext Message »
From:Mike Blezien Date:May 17 2006 2:21pm
Subject:Re: Baffled by query error syntax
View as plain text  
Hi Sheeri,

Is your 'u.modified' column a TIME datatype '00:00:00'

----- Original Message ----- 
From: "sheeri kritzer" <awfief@stripped>
To: "Mike Blezien" <mickalo@stripped>
Cc: "MySQL List" <mysql@stripped>
Sent: Wednesday, May 17, 2006 9:10 AM
Subject: Re: Baffled by query error syntax


I can't really help except to ask if you're sure you copied and pasted
the query correctly.  I did a similar query against a test system:

select u.uid,u.username,b.buddyUid,SEC_TO_TIME(SUM(TIME_TO_SEC(u.modified)))
as mins from Users u left join BuddyList b on u.uid = b.uid where
u.modified >= DATE_SUB(NOW(),INTERVAL 14 DAY) and country="au" group
by u.uid having mins >= '2' order by mins;

Similar joins, similar where clause, etc and yet I got an answer
(almost 700 rows, took 2 seconds) while you got a syntax error.
select @@version;
| @@VERSION           |
| 4.1.12-standard-log |

So I'm not sure what to recommend other than trying the query again to
make sure there aren't typos.

MySQL usually gives a syntax error *where* the error happens.  In this
case, it would indicate a problem with "SEC_TO_TIME(" but there
shouldn't be a problem, both according to the manual AND according to
my example.

I would prepare for a bug report -- create 2 new tables in the test
db, in this case you don't need a lot of test data, do the join, and
if you still get the problem, submit a bug report (you've just done
the "steps to recreate" part).  Many times I've done this and realized
where my bug was because the query worked in the test table.


On 5/17/06, Mike Blezien <mickalo@stripped> wrote:
> Hello,
> this is a continued problem we are having from a earlier posting to the list
> regarding a query. We need to calculate the SUM of the column
> 'agent_product_time' which is a TIME datatype column and according to the
> manual:
> this is the way to SUM the total time, which keeps producing a syntax error 
> and
> figure out why
> MySQL version 4.1.12
> ---------------------------------------------------------------------------------------------------
> SELECT c.account_id,,,
> SEC_TO_TIME(SUM(TIME_TO_SEC(c.agent_product_time))) AS mins
> FROM account a LEFT JOIN calls c ON c.account_id =
> WHERE c.calldate >= DATE_SUB(NOW(),INTERVAL 14 DAY)
> AND c.agent_id = 2 GROUP BY c.account_id HAVING mins >= '500' ORDER BY mins
> #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
> '( SUM( TIME_TO_SEC( c . agent_product_time ) ) ) AS mins  FROM account a LEFT
> JO' at line 1
> --------------------------------------------------------------------------
> What would be producing the syntax error here.??
> Again, any help would be much appreciated.
> Mike(mickalo)Blezien
> ===============================
> Thunder Rain Internet Publishing
> Providing Internet Solution that Work
> ===============================
> --
> MySQL General Mailing List
> For list archives:
> To unsubscribe:

Baffled by query error syntaxMike Blezien17 May
  • Re: Baffled by query error syntaxsheeri kritzer17 May
    • Re: Baffled by query error syntaxMichael Stassen18 May
  • Re: Baffled by query error syntaxMike Blezien17 May
  • Re: Baffled by query error syntaxMichael Stassen18 May