SQL: return string describing user category based on a field

Question

The idea is to get in the result a new column describing the user "category". This category could for example be "under18","normal","over65" based on user age.

That is, for each user in my table I want to get as result its ID and this description in a column called "category".

How would you proceed? I need this because the data will later go to a JSON object and to a ExtJS grid for visualization.

Answer 1

As your category is data derived from existing data, the use of a new column in the database for it should be discouraged. Instead, you could just add a case statement to your database query to work out the category as required or put it into a view in the database.

The query would look something like this:

select
    ...
    case when Age < 18 then 'under18'
         when Age > 65 then 'over65'
         else 'normal' end AS Age_Category
from ...

Answer 2

At query level this can be done with CASE expression:

select
  user_id,
  case when age < 18 then 'under18'
       when age <=65 then 'normal'
       when age > 65 then 'over65'
       else null end as category
from
  Users

However i would recommend to move this from query level to application level. You already have application logic to construct JSON from the dataset, this would be a more appropriate place to generate category name from age value.

Answer 3

If your DBMS supports it, I would create a UDF (user defined function) that passes in the age from your table and returns category. This has the advantage of being reusable and a little cleaner. If this is for one query only just use the CASE statement.