Search code examples
mysqlsqlmysql-error-1111

SQL: order by count from different table with condition

I want to display the results such that:

record number is ordered by the number of items attached to it

IF

the number of items attached is greater than 100

SELECT r.number,
       r.title,
       COUNT(i.itemnumber)
FROM record r
LEFT JOIN items i ON (r.number = i.number)
WHERE r.title REGEXP 'SQL for idiots'
AND COUNT(i.itemnumber) > 100
GROUP BY r.number
ORDER BY COUNT(i.itemnumber)

This throws the old:

ERROR 1111 (HY000): Invalid use of group function

I've tried a 'HAVING' clause with no luck either.

Got it!:

Had to swap lines with my ORDER clause:

HAVING COUNT(i.itemnumber) > 100
ORDER BY COUNT(i.itemnumber)
Solution

  • You test the results of an aggregate function with a HAVING clause.

    SELECT r.number,
       r.title,
       COUNT(i.itemnumber)
FROM record r
LEFT JOIN items i ON (r.number = i.number)
WHERE r.title REGEXP 'SQL for idiots'
GROUP BY r.number
HAVING COUNT(i.itemnumber) > 100
ORDER BY COUNT(i.itemnumber)