Mike Blezien 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.??
Something is strange here. The piece of your query quoted in the syntax error
does not match the query you gave us. That makes me think you've given us an
edited version of your query. It's hard to catch a syntax error if you don't
give us the actual query.
The piece of the query quoted in the error has a lot of extraneous spaces. If I
had to guess, I'd bet that there is a space between SEC_TO_TIME and the opening
parenthesis in your real query. That is, you have
SEC_TO_TIME ( SUM...
instead of
SEC_TO_TIME(SUM...
The parser distinguishes functions from columns by the presence of a parenthesis
attached to the function name. For example:
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 4.1.15 |
+-----------+
1 row in set (0.00 sec)
but
mysql> SELECT VERSION ();
ERROR 1064 (42000): 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 '()' at line 1
Note that the error message quotes the query starting with the opening
parenthesis, as is the case for you.
If that isn't it, please copy and paste your actual query into your next
message. I'm sure someone will spot the problem.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
