Mike,
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.
-Sheeri
On 5/17/06, Mike Blezien <[EMAIL PROTECTED]> 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:
http://dev.mysql.com/doc/refman/4.1/en/date-and-time-type-overview.html
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,a.name,a.company,
SEC_TO_TIME(SUM(TIME_TO_SEC(c.agent_product_time))) AS mins
FROM account a LEFT JOIN calls c ON c.account_id = a.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
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
'( 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
http://www.thunder-rain.com
===============================
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
