Combining Aggregates II syntax question


#1


https://www.codecademy.com/courses/sql-table-transformation/lessons/conditional-aggregates/exercises/combining-aggregates-ii?action=lesson_resume&link_content_target=interstitial_lesson


Hello all,
In this lesson the percentage of high-elevation airports needs to be found per state. My confusion is coming from having an altered outcome when parenthesis are added.

100.0 * high_elevation_airports / total_airports

is different than:

100.0 * (high_elevation_airports / total_airports)

Can someone please help me understand this?
The correct code vs. my code is listed below.
Thanks.



select state, 
    100.0 * sum(case
                  when elevation >= 2000 then 1
                  else 0
       		end) / count(*) as percentage_high_elevation_airports
from airports
group by state;

select state, 
    100.0 * (sum(case
                   when elevation >= 2000 then 1
                   else 0
             end) / count(*)) as percentage_high_elevation_airports
from airports
group by state;


#2

Never mind I think I get it.

With parenthesis it divides the two integers together first, dropping whatever decimal they would have had. Without parenthesis it evaluates left to right, and since the first number is a decimal (100.0) it implicitly casts the remaining integers as decimals before evaluating them.

Looks like you can keep the parenthesis as long as one of the ints inside is cast as a float.
E.G.:

cast(high_elevation_airports as float)

#3

The above example is a float divided by an integer.

Here above it is a float times a possible integer, because first it is two integers divided, which may (can't say for sure) resolve to an integer before the multiplication.

Eg.

100.0 * 9 / 2    =>  450.0

100.0 * (9 / 2)  =>  400.0

This is just speculation mind; it's the way Python 2 does it.


#4

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.