prestodb: simple IF condition to create a column

Gerry 0
Viewed 19

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

amazon-athena presto sql
1 Answers

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.

Guru Stron0
Related