Can't figure out how to properly use CASE WHEN statement in a ORDER BY clause

Question

I get an error on pgadmin query tool saying that deelnemers (it's a dutch database) doesn't exist. However as far as I understand sql queries the SELECT should be executed before the ORDER BY.

I have already tried changing the order of the CASE WHEN statement but I keep getting this error.

SELECT r.reisnr , COUNT (k.naam) AS deelnemers
FROM ruimtereizen.reizen r
INNER JOIN ruimtereizen.deelnames d
USING (reisnr)
INNER JOIN ruimtereizen.klanten k
USING (klantnr)
GROUP BY r.reisnr
ORDER BY CASE WHEN COUNT (DISTINCT deelnemers) > 1 THEN deelnemers
WHEN COUNT (DISTINCT deelnemers) = 1 THEN r.reisnr
ELSE deelnemers
END

The error is that deelnemers doesn't exist.

Answer 1

As a general answer I would say your "generic SQL" is good. But... every database supports some features and fails to support other ones.

Anyway, to be on the safe side I would pre-compute your COUNT() function in a CTE so I could use it freely on the outer query. For example:

with
x as (
  SELECT 
    r.reisnr, 
    COUNT (k.naam) AS deelnemers,
    COUNT (DISTINCT deelnemers) as dd
  FROM ruimtereizen.reizen r
  INNER JOIN ruimtereizen.deelnames d
  USING (reisnr)
  INNER JOIN ruimtereizen.klanten k
  USING (klantnr)
  GROUP BY r.reisnr
)
select * 
from x
ORDER BY CASE WHEN dd > 1 THEN deelnemers
              WHEN dd = 1 THEN reisnr
              ELSE deelnemers
              END