I don't found any issue in your query. Please share the MySQL version.

You can update your 2nd query for a better output, as follows:

SELECT CONCAT('There are a total of',' ',COUNT(occupation),' ', LOWER(occupation),IF(COUNT(occupation)>1, 's', ''),'.') AS OCCUPATION_COUNT 
FROM OCCUPATIONS 
GROUP BY OCCUPATION 
ORDER BY COUNT(OCCUPATION) ASC;

attached db<>fiddle code for reference.