Strange Mysql error 1111, supposedly worked before...

Question

I am trying to troubleshoot a particular 'report' that gets generated on a PHP application running on a VOIP platform (billing related). The person that asked me to do this stated "it worked before" ;)

MySQL Error thrown: 1111: Invalid use of group function

Crazy thing is, it 'worked before' but stopped after awhile, they cannot place what event took place that might have created this bug. One thought is maybe MYSQL was upgraded? I googled the error, and can't seem to find a clear answer.

At first glance do you guys see anything wrong here in the SQL?

SELECT C.ResourceGroupID AS CustomerID, CS.CustomerName, SUM(C.IN_RndDuration/60) AS Minutes, SUM(CASE WHEN (C.OUT_Duration>0 AND C.IN_RndDuration>0) THEN 1 ELSE 0 END) AS Successfull, count(0) AS Attempts     
FROM ws_call_current AS C 
LEFT JOIN customer_rg AS V_RG ON 
V_RG.RGId=C.OtherResourceGroupID AND V_RG.RateTableId IS NOT NULL AND V_RG.Direction='O'
LEFT JOIN customer AS V ON V.CustomerId=V_RG.CustomerId 
LEFT JOIN customer_rg AS CS_RG ON CS_RG.RGId=C.ResourceGroupID AND CS_RG.RateTableId IS NOT NULL AND CS_RG.Direction='I'     
LEFT JOIN customer AS CS ON CS.CustomerId=CS_RG.CustomerId     
WHERE DATE_FORMAT(DATE_ADD(C.SeizeDate, INTERVAL TIME_TO_SEC(C.SeizeTime) SECOND), '%Y-%m-%d %H:%i:00') BETWEEN '2010-10-19 00:00:00' AND '2010-10-19 23:59:59'      
AND C.SeizeDate BETWEEN '2010-10-19' AND '2010-10-19'      
GROUP BY CS.CustomerName     
ORDER BY SUM(C.IN_RndDuration/60) DESC

This is written for MYSQL 3/4 I believe, not yet sure. Just wanted to get some feedback. From google results I find some people had success with fixing this by modifying the query to be a Having instead of a where?

Not sure. Anything look odd below?

Answer 1

Try including C.ResourceGroupID in your group by statement. You need to include all non-aggregate items in your select in your group by.

You shouldn't need a having in the code above.

I'm not sure how it could have worked before...

Answer 2

What I found after review is that the cause of this was simply a MySQL discrepancy. Initially the MySQL code was written for 5.0.51a, and the running MySQL version (it was somehow 'attempted' to be upgraded, was 5.0.21).

This version 'downgrade' was the cause of the failed SQL. I honestly don't know what the cause was other than we installed a new version of MySQL to just do a test (on a remote box) and it solved the issue (pointing from primary box to backup MySQL server.

Solution for this error: Version compatibility