MySQL Error 1111: Invalid Function when using nested COUNT operations

Other Questions MySQL
1

I’m experiencing an issue with my MySQL query that worked correctly until I added a new line of computation. Specifically, the issue began with the introduction of this line in my SELECT statement:

COUNT(0) * COUNT(MAX(hdmhdrprice.dmduprice)) AS 'Total Quantity'

The affected portion of my query looks like this:

COUNT(MAX(hdmhdrprice.dmduprice)) AS 'Unit Value',
COUNT(0) AS 'On Hand per Count',
COUNT(0) * COUNT(MAX(hdmhdrprice.dmduprice)) AS 'Total Quantity'

Now, I am facing an ErrorNr. 1111 with my entire query:

SELECT CONCAT(hgen.gendesc, ',', 
(CASE WHEN hdmhdr.brandname IS NULL THEN '' ELSE CONCAT(' ', hdmhdr.brandname) END),
(CASE WHEN hdmhdr.dmdnost IS NULL THEN '' ELSE CONCAT(' ', CONVERT(hdmhdr.dmdnost, CHAR(14))) END),
(CASE WHEN hstre.stredesc IS NULL THEN '' ELSE hstre.stredesc END),
(CASE WHEN hform.formdesc IS NULL THEN '' ELSE CONCAT(' ', hform.formdesc) END),
(CASE WHEN hroute.rtedesc IS NULL THEN '' ELSE CONCAT(' ', hroute.rtedesc) END)) AS 'itemdesc',
(CASE WHEN hform.formdesc IS NULL THEN '' ELSE CONCAT(' ', hform.formdesc) END) AS 'Unit of Measurement',
hdmhdr.dmdcomb AS 'Stock No:',
COUNT(MAX(hdmhdrprice.dmduprice)) AS 'Unit Value',
COUNT(0) AS 'On Hand per Count',
COUNT(0) * COUNT(MAX(hdmhdrprice.dmduprice)) AS 'Total Quantity'
FROM hdmhdr
LEFT JOIN hroute ON hdmhdr.rtecode = hroute.rtecode
LEFT JOIN hstre ON hdmhdr.strecode = hstre.strecode
INNER JOIN hdruggrp ON hdruggrp.grpcode = hdmhdr.grpcode
INNER JOIN hform ON hform.formcode = hdmhdr.formcode
INNER JOIN hgen ON hgen.gencode = hdruggrp.gencode
INNER JOIN hdmhdrprice ON hdmhdrprice.dmdcomb = hdmhdr.dmdcomb
WHERE hdmhdr.dmdcomb IS NOT NULL
GROUP BY itemdesc

What could be causing this function error? The query was running smoothly prior to adding these aggregate computations.

2

u might be nesting those aggrg functions wrong - mysql isn’t a fan of that. try breaking it into a subquery or handle that calc outside the query. that COUNT(MAX()) pair is def causing that 1111 error.

3

MySQL doesn’t support nested aggregate functions like COUNT(MAX()) - that’s your main problem. I hit this same issue working on inventory calculations and it took me forever to figure out. Your COUNT(MAX(hdmhdrprice.dmduprice)) doesn’t make sense anyway. COUNT always returns 1 when you apply it to a single MAX result per group. Just use MAX(hdmhdrprice.dmduprice) if you want the max price. For Total Quantity, you’ll need to completely rethink this. Multiplying COUNT(0) by a nested aggregate is what’s throwing the 1111 error. Try using a derived table instead - calculate your MAX values first, then reference those in your outer query for whatever else you need to calculate.

4

You’re encountering MySQL error 1111 because nesting aggregate functions like COUNT(MAX()) is problematic. MySQL cannot resolve this, as MAX operates on grouped data, and wrapping COUNT around it creates a conflict. To resolve the issue, consider using a subquery to compute the MAX values first and then apply COUNT in the outer query. Alternatively, reassess your requirements; if your goal is to obtain the maximum price per group, simply use MAX without a COUNT wrapper. Additionally, your COUNT(0) multiplication in the Total Quantity calculation exacerbates the issue.