----- Original Message -----
From: "Rhino" <rhino1@stripped>
To: "Mike Blezien" <mickalo@stripped>; "MySQL List"
<mysql@stripped>
Sent: Tuesday, May 16, 2006 1:56 PM
Subject: Re: Baffled by error
>
> ----- Original Message -----
> From: "Mike Blezien" <mickalo@stripped>
> To: "MySQL List" <mysql@stripped>
> 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/mysql?unsub=1
>
>
> --
> 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
