PrestoSQL Why can't i divide an aggregating function inside a case statement?

Question

I am getting null for some of the rows in Amazon Athena when the absolute difference is bigger than 20 percent. I am not sure I understand the logic of why. Can someone explain?

case
when gla_sqft = AreaBuilding then 0
when ((abs(AreaBuilding- gla_sqft)/gla_sqft) > 0.2) then 1 else null
end as percent_diff_logic

AreaBuilding	gla_sqft	percent_diff	percent_diff_logic
1498	2100	0.2866666666666667

What's the data type on your columns? AreaBuilding- gla_sqft)/gla_sqft. Any chance you have nulls in either areabuilding or gla_sqft? — VLOOKUP, Sep 28 '22 at 21:36

score 0 · Answer 1 · answered Sep 28 '22 at 21:37

Most likely you are getting null because there are rows where (abs (AreaBuilding-gla_sqft) / gla_sqft) is less than or equal to 0.2. Performing a quick select query (SELECT ... WHERE ((abs (AreaBuilding-gla_sqft) / gla_sqft) <= 0.2)) should eliminate my assumption.

score 0 · Answer 2 · answered Sep 29 '22 at 13:50

It seems that you have standard case of integer division, i.e. 602/2100 is equal to 0. Just multiply result of abs by 1.0 or cast it to double:

when ((abs(AreaBuilding- gla_sqft) * 1.0 /gla_sqft) > 0.2) then 1 else null

or

when ((cast(abs(AreaBuilding- gla_sqft) as double)/gla_sqft) > 0.2) then 1 else null

PrestoSQL Why can't i divide an aggregating function inside a case statement?

2 Answers2