Combining aggregates and Combining aggregates ||


#1


Hello, can somebody explain me why we use divide by "sum(distance)" in task Combining aggregates when we need to find "percentage_flight_distance_from_delta", and divide by count(*) in task Combining aggregates || when we need to find "percentage_high_elevation_airports" ?


https://www.codecademy.com/en/courses/sql-table-transformation/lessons/conditional-aggregates/exercises/combining-aggregates-i



Replace this line with your code.


#2

Let's say that all distances combined for all flights is the SUM(distance), and combined distances of only one carrier equal another sum that will be less than this (of course).

 DL_distance
-------------- * 100.0
toal_distance

This gives a percentage of distance.

high_elev_airports_count
------------------------- * 100.0
   all_airports_count

This gives the total percentage of airports at high elevations ( > 2000 ft).


#3

Syntax question:. The code I wrote was:

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

while the solution is

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;

The issue is the parenthesis around the fraction. I used it as in the preiovus example, where the denominator was the function sum(distance), in this case it is count(*). Why is it wrong here?


#4

I wouldn't say it is 'wrong' just not a match to the pattern expected by the lesson checker (SCT). This can and does happen in many lessons where the SCT is not exhaustive. You know it is correct if your got a table of results and no SQL error messages.


#5

Thank you!:slight_smile:


#6

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