Syntax in 6. Combining Aggregates II


#1

The syntax needed for the correct answer in 6. is confusing. Here is the example from the previous lesson that we're supposed to model:

SELECT
origin,
100.0*(SUM(CASE WHEN carrier = 'DL' THEN distance ELSE 0 END)/SUM(distance)) as percentage_flight_distance_from_delta
FROM flights
GROUP BY origin;

After trial and error, this was the final answer I attempted for 6. before giving up and getting the code:

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;

And here is the code for the correct answer:

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 only difference I see in the code for the correct answer and my code is that I enclosed the calculation after "100.0*" in parentheses - which is exactly what the example from the previous lesson would led me to believe is the correct syntax. But for this code to run, those parentheses needed to be removed.

What is the explanation for this? It's frustrating to know that my answer was essentially correct save for a syntax error, but then seeing the same syntax error in a previous example that worked.


Instructions
#2

@webcoder37003,
The difference i see is
if you use

100.0 * sum(CASE WHEN elevation >= 2000 THEN 1 ELSE 0 END)

you would end-up with a =float= number BEFORE you use the division operator.......


#3

Your code will work with the parentheses in if you put a decimal point after the "0".
i.e
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;

I think it's something to do with the code picking it up as an integer so it won't calculate the percentage accurately. By putting in the parentheses along with the decimal point seems to fix this.


#4