----- Original Message -----
From: "Rhino" <[EMAIL PROTECTED]>
To: "Mike Blezien" <[EMAIL PROTECTED]>; "MySQL List"
<mysql@lists.mysql.com>
Sent: Tuesday, May 16, 2006 1:56 PM
Subject: Re: Baffled by error
----- 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
Sorry, I didn't notice the name in the SELECT. Therefore, the GROUP BY
should be:
GROUP BY a.account_id, a.name, 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.
Oops, that should be:
DB2 would expect you to put ALL of the other THREE 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
Oops, that should be:
Now, if each account_id is associated with a single name and company, this
should
give you the result you probably envisioned. But if a given account_id can
be associated with more than one company and the company with more than one
name, then your revised query will give you one sum for each COMBINATION of
account_id, name and company. For example:
ACCOUNT_ID NAME COMPANY MINS
001 Bob ABC Co. 45
001 Dave ABC Co. 20
001 Mary DEF Ltd. 90
001 Jane DEF Ltd. 190
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]
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.6/340 - Release Date: 15/05/2006
--
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]
