Mind the integer! Lesson 6/8


If you are doing the exercise you will notice the "GET CODE" gives you this:

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;

And actually you get an unexpected result.

Why? Because 'distance' has an integer property and an extra bracket after 100.0 makes everything inside the bracket an integer.

So, all the fractions inside the bracket becomes integer say 0.66 will become 0. And multiplying 0 with 100.0 gives you 0 percent when it should be 66 percent! But if you do not use the bracket after 100.0 then 100.0 the real value first get multiplied and you get the result as a real value. The attached image actually shows the correct procedure.

So, remove the first bracket set after 100.0 and you are good to go.

Wrong "correct" result for 6. Combining aggregrates