List:General Discussion« Previous MessageNext Message »
From:Gordon Date:May 16 2006 8:17pm
Subject:RE: Baffled by error
View as plain text  
Try this 

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
GROUP BY a.account_id
HAVING   SUM(c.agent_product_time) >= '500'
ORDER BY mins

You have to do the SUM with the GROUP BY before you can test the
criteria

-----Original Message-----
From: Mike Blezien [mailto:mickalo@stripped] 
Sent: Tuesday, May 16, 2006 10:30 AM
To: MySQL List
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...

Mike(mickalo)Blezien
===============================
Thunder Rain Internet Publishing
Providing Internet Solution that Work
http://www.thunder-rain.com
===============================

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1



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