----- Original Message -----
From: "Mike Blezien" <[EMAIL PROTECTED]>
To: "MySQL List" <mysql@lists.mysql.com>
Sent: Tuesday, May 16, 2006 1:29 PM
Subject: Baffled by error
Hello,
MySQL 4.1.12
trying to figure out why I keep getting this error with the following
query:
SELECT c.account_id,a.name,a.company,SUM(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 = 9
AND SUM(c.agent_product_time) >= '500' GROUP BY a.account_id
ORDER BY mins
ERROR: #1111 - Invalid use of group function
Any help appreciated...
I mostly use DB2, not MySQL, and I know that MySQL is a bit looser about
what it will allow in its SQL. However, if this were happening in DB2, I'd
say that it's because you needed to change the GROUP BY to say:
GROUP BY a.account_id, a.company
In DB2, the GROUP BY normally needs to include EVERY column/expression from
the SELECT clause that is not in a column function. In your case, you are
doing a SUM on c.agent_product_time so that means DB2 would expect you to
put BOTH of the other two columns from the SELECT in your GROUP BY.
Now, if each account_id is associated with a single company, this should
give you the result you probably envisioned. But if a given account_id can
be associated with more than one company, then your revised query will give
you one sum for each COMBINATION of account_id and company. For example:
ACCOUNT_ID COMPANY MINS
001 ABC Co. 45
001 DEF Ltd. 90
But maybe that's what you wanted all along?
--
Rhino
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.6/340 - Release Date: 15/05/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
