milos milos - 28 days ago 15
MySQL Question

Why doesn't ORDER BY work in this case?

I want to print some stats so I have the following code:

(SELECT 'There are total', COUNT(Name), 'doctors.' FROM OCCUPATIONS
WHERE Occupation = 'Doctor')
UNION
(SELECT 'There are total', COUNT(Name), 'singers.' FROM OCCUPATIONS
WHERE Occupation = 'Singer')
UNION
(SELECT 'There are total', COUNT(Name), 'actors.' FROM OCCUPATIONS
WHERE Occupation = 'Actor')
UNION
(SELECT 'There are total', COUNT(Name), 'professors.' FROM OCCUPATIONS
WHERE Occupation = 'Professor')
ORDER BY COUNT(Occupation);


It counts the occurrences of each occupation. I also need to sort these 4 lines corresponding to each profession by the occurrences. My code does not seem to work however:

ERROR 1054 (42S22) at line 4: Unknown column 'Occupation' in 'order clause'


What is wrong and how to fix it?

Answer

You need to alias COUNT(Name) and use that alias in the ORDER BY:

(SELECT 'There are total', COUNT(Name) OccupationCount, 'doctors.' FROM OCCUPATIONS
WHERE Occupation = 'Doctor')
UNION
(SELECT 'There are total', COUNT(Name), 'singers.' FROM OCCUPATIONS
WHERE Occupation = 'Singer')
UNION
(SELECT 'There are total', COUNT(Name), 'actors.' FROM OCCUPATIONS
WHERE Occupation = 'Actor')
UNION
(SELECT 'There are total', COUNT(Name), 'professors.' FROM OCCUPATIONS
WHERE Occupation = 'Professor')
ORDER BY OccupationCount;

However, you don't have to use UNION for these. You could easily use COUNT and GROUP BY with a WHERE clause:

SELECT
    'There are total',
    COUNT(Name) OccupationCount,
    CONCAT(LOWER(Occupation), 's')
FROM OCCUPATIONS
WHERE Occupation IN('Doctor', 'Singer', 'Actor', 'Professor')
GROUP BY Occupation
ORDER BY OccupationCount;