Combining aggregates


#1

When trying to copy code from the learn part, i just get the result of 0.0 an 100.0.

Then i try some code and get this solution to overcome that problem:

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

I think I just get result of 0.0 an 100.0 is because the brackets around

(sum(CASE WHEN carrier = 'UN' THEN distance ELSE 0 END)/sum(distance))

make the calculation doing that first and will result 0 or 0,(some number) or 1. And because distance is an integer then the result from calculation above will give an integer too, that is 0 or 1 , not give decimal result. Also, there is no UN origin in the database, but there is UA.
So the solution is to remove those brackets. This step will make the calculation to compute

100.0*sum(CASE WHEN carrier = 'UA' THEN distance ELSE 0 END)

first, that will give decimal result and thats all. :relaxed:

Hope this can help

Sorry for my bad english.




#2

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