I was create this query:

select first_price, last_price, cast((sum(1 - (first_price / nullif(last_price,0)))) as double) as first_vs_last_percentages 
from prices 
group by first_price, last_price 
having first_vs_last_percentages >= 0.1 

unfortunately this is my wrong data in first_vs_last_percentages col

It was supposed to return this output:

if someone has a good solution and it will be in presto syntax it will be wonderful.

CodePudding user response：

It seems you got struck by another case of integer division (your cast to double is a bit late), update the query so the divisor or dividend type changes (for example by multiplying one of them by 1.0 which is a bit shorter then cast to double):

select -- ...
  , sum(1 - (first_price * 1.0) / nullif(last_price, 0)) first_vs_last_percentages
from ...

P.S.

Your query is a bit strange, not sure why do you need grouping and sum here.

CodePudding user response：

It depends on which database engine you work upon. Typically, most query confusion rely on either conceptual or syntatic mistakes. In either one or the other cases, it seek to operate a row-percentage double 100.0*(last-first)/first. It means, you can drop the group by and having, since we MUST NOT group by double values, rather intervals they belong.

select 
    first_price, 
    last_price,  
    CASE
        WHEN first_price = 0 THEN NULL
        ELSE (last_price-first_price)/first_price
    end as first_vs_last_percentage 
from prices