List:General Discussion« Previous MessageNext Message »
From:Rhino Date:May 16 2006 5:56pm
Subject:Re: Baffled by error
View as plain text  
----- 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

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

Thread
Baffled by errorMike Blezien16 May
  • Re: Baffled by errorRhino16 May
  • Re: Baffled by errorChris Sansom16 May
  • RE: Baffled by errorIng. Edwin Cruz16 May
    • Re: Baffled by errorMike Blezien16 May
  • Re: Baffled by errorMike Blezien16 May
  • Re: Baffled by errorPeter Brawley16 May
  • Re: Baffled by errorRhino16 May
  • Re: Baffled by errorMike Blezien16 May
    • Re: Baffled by errorPeter Brawley16 May
  • Re: Baffled by errorMike Blezien16 May
  • RE: Baffled by errorGordon16 May
    • Re: Baffled by errorMike Blezien16 May