Search code examples
sqlpostgresqlcase

How to write a SQL CASE statement without selecting the case column?

I am trying to get the below CASE statement to work:

SELECT name
CASE
    WHEN (monthlymaintenance > 100) then 'expensive'
    ELSE 'cheap'
END cost
FROM cd.facilities

It fails with the following error:

ERROR: syntax error at or near "CASE"
  Position: 33

 Query was: SELECT name, monthlymaintenance
CASE
    WHEN (monthlymaintenance > 100) then 'expensive'
    ELSE 'cheap'
END cost
FROM cd.facilities

Source of the SQL puzzle is: https://pgexercises.com/questions/basic/classify.html

Solution

  • for this query you are missing a comma after the 'name' column

    SELECT name,
CASE
    WHEN (monthlymaintenance > 100) then 'expensive'
    ELSE 'cheap'
END cost
FROM cd.facilities