I'm very new to Presto and SQL, and struggling with something that might be basic.
I have a simple table: id and number_of_customers
I'd like to do a select that computes a third column and puts certain flags depending on number_of_customers, for e.g., if num > 1000, the computed column would have A, if it's > 10000 then B etc - I intend to have 4-5 such flags.
id num
1 200000
2 40000
and select result being
1 200000 A
2 40000 B
Can't figure out how to do this. Examples I see for IF and CASE are too complicated.
Edit: this is what I have:
SELECT name, count as num
case
when num < 1000 then 'A'
when num > 10000 then 'B'
else 'X'
end
FROM dbs
limit 10
Thank you
CodePudding user response:
This is a standard use case for CASE
expressions. Depending on actual flags it can look like:
-- sample
with dataset(id, num) as (
values (1, 200000),
(2, 40000)
)
-- query
select *,
case
when num < 1000 then 'A'
when num <= 40000 then 'B'
else 'X'
end
from dataset;
Output:
id | num | _col2 |
---|---|---|
1 | 200000 | X |
2 | 40000 | B |
Note that order of conditions matters, if there are multiple conditions that match the first will be used.