How do I use ranges with Presto DB

Question

I want to do the following, which I found for SQL Server, using PrestoDB?

select t.range as 'latency', count(*) as 'Total'
from (
  select case  
    when latency between 0 and 0.250 then 'Fast'
    when latency between 0.250 and 1.0 then 'Normal'
    when latency between 1.0 and 2.0 then 'Elevated'
    else 'High' 
  end as range
  from myprestodb.mytable) t
group by t.range

... so that I get result like this:

latency        | Total
-------------------------------------
   Fast        |        11
   Normal      |        14
   Elevated    |         3
   High        |         1

score 1 · Answer 1 · answered Dec 14 '18 at 17:12

1

You can try:

select range as Latency, count(*) as Total
from (
  select case  
    when latency > 0 and latency <= 0.250 then 'Fast'
    when latency > 0.250 and latency <= 1.0 then 'Normal'
    when latency > 1.0 and latency <= 2.0 then 'Elevated'
    else 'High' 
  end as range
  from myprestodb.mytable
)
group by range

answered Dec 14 '18 at 17:12

dtsellos03

43
5

Thanks for providing that. I tried some version of this but didn't work but will give this a try. I did find that `count_if` was what I needed. – Craig Conover Dec 17 '18 at 19:42

score 1 · Accepted Answer · answered Dec 17 '18 at 19:42

You can use count_if to provide conditionals on what gets counted per column. This was precisely what I was looking for:

select count_if(latency < 0.25) as "Fast: < .25", 
    count_if(latency > 0.25 and latency <= 1.0) as "Normal: .25 - 1", 
    count_if(latency > 1.0 and latency <= 2.0) as "Elevated: 1 - 2", 
    count_if(latency > 2.0) as "High: > 2"

from myprestodb.mytable
...

How do I use ranges with Presto DB

2 Answers2