How to create the column with conditions?-CodePudding

Say I have table with 3 columns - id, quantity, price. Let's quantity is 1 - 10000. I want to split/group the 2d column on 5 parts and add a varchar column with explanation for these intervals:

1-200     --- critical
201-400   --- small
401-600   --- medium
601-800   --- enough
801-1000  --- ok

So, the output consists of 4 column - id, quantity, price and explanation, grouped by quantity. How to do it with Postgres?

CodePudding user response：

You can use CASE in a view or add a virtual column using CASE, or simply use CASE in your query.

Select 
  id, 
  quantity, 
  price,
  case quantity
    When <= 200 then 'critical'
    When <= 400 then 'small'
    When <= 600 then 'medium'
    When <= 800 then 'enough'
    Else 'ok' as status
From myTable;

CodePudding user response：

Did you want

SELECT id, quantity, price,
CASE
 WHEN (quantity <= 200) THEN 'critical' 
 WHEN (quantity BETWEEN 201 AND 400) THEN 'small' 
 WHEN (quantity BETWEEN 401 AND 600) THEN 'medium' 
 WHEN (quantity BETWEEN 601 AND 800) THEN 'enough' 
 ELSE 'ok' 
END AS explanation
FROM ...