"ORDER BY CASE..." causing Invalid expression in ORDER BY clause

Question

Can anyone point out what is causing me to get the

Invalid expression in the ORDER BY clause (not contained in either an aggregate function or the GROUP BY clause)

error on the following query? This is in Firebird 2.5.

SELECT  COUNT(*) AS total,
        CASE WHEN YR BETWEEN 1990 AND 1995 THEN '1990-1995' 
             WHEN YR BETWEEN 1996 AND 2000 THEN '1996-2000'
             WHEN YR BETWEEN 2001 AND 2005 THEN '2001-2005'
             ELSE '2005-2017' END AS vehicle_year
FROM bb_history
GROUP BY 2
ORDER BY CASE WHEN YR BETWEEN 1990 AND 1995 THEN 1
              WHEN YR BETWEEN 1996 AND 2000 THEN 2
              WHEN YR BETWEEN 2001 AND 2005 THEN 3
              ELSE 4 END

Answer 1

You can't use YR column in order by because it is not part of the result of grouping. It seems you just want to order by the generated vehicle_year column, so you can just order by that (or by 2):

SELECT  COUNT(*) AS total,
        CASE WHEN YR BETWEEN 1990 AND 1995 THEN '1990-1995' 
             WHEN YR BETWEEN 1996 AND 2000 THEN '1996-2000'
             WHEN YR BETWEEN 2001 AND 2005 THEN '2001-2005'
             ELSE '2005-2017' END AS vehicle_year
FROM bb_history
GROUP BY 2
ORDER BY vehicle_year;

Answer 2

You have only two selected column why you added '3' and '4', but here is the query:

SELECT  COUNT(*) AS total,
        CASE WHEN YR BETWEEN 1990 AND 1995 THEN '1990-1995' 
             WHEN YR BETWEEN 1996 AND 2000 THEN '1996-2000'
             WHEN YR BETWEEN 2001 AND 2005 THEN '2001-2005'
             ELSE '2005-2017' END AS vehicle_year
FROM bb_history
GROUP BY 2
ORDER BY CASE WHEN vehicle_year BETWEEN 1990 AND 1995 THEN 1
              WHEN vehicle_year BETWEEN 1996 AND 2000 THEN 2
              WHEN vehicle_year BETWEEN 2001 AND 2005 THEN 3
              ELSE 4 END