Combines agregats II


#1

I get answer:
SELECT state,
100.0*(sum(CASE WHEN elevation >= 500 THEN elevation ELSE 0 END)/sum(elevation)) as percentage_high_elevation_airports FROM airports
GROUP BY state;

But in table with result i will see only 0 or 100,0 %, no value 53,2 or like simlar....
You too?
Looks i'm wrong in solving.


#2

@bytemaster90052,
And you are sure you do not want to

  • count the airports which have an elevation >= 500 by using a =trick=
    • CASE WHEN elevation >= 500 THEN 1 ELSE 0 END
    • you can then sum() the zero's and one's
  • count all airports by using
    • count(*)

#3

@bytemaster90052,
You were right.....about only having 100 or 0

The cause is the division by two integers will by default give an integer....

If you want to see the real percentages
you might want to use

SELECT state, 
       sum(CASE WHEN elevation >=2000 THEN 1 ELSE 0 END) as HA,
       count(*) as TA,
       printf("%.2f",
              100.0*(sum(CASE WHEN elevation >=2000 THEN 1 ELSE 0 END)/
               cast(count(*) as double))) as 
               percentage_high_elevation_airports 
FROM airports
GROUP BY state;

#4

I get the same problem. I believe it is because in Combine Aggregats I they give you and pass the wrong format. Since Combine Aggs II builds off the same principle, you get an error.


#5

Thank you! I try it!


#6

I was getting the same problem with mine. Only displays 0.00 or null in the output for Table Transformation EX 7. I'm finding several bugs on the site.


#7

Try this code, it worked for me:

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;


#8

While you're at it, ROUND it. You'll notice some of the decimal places are too far down (like 63.8768456) so the solution is to round them to the second decimal place. The thing with Rounding is to parenthesis the whole equation, then put the decimal place identifier after a comma at the end then, close parenthesis.
EXAMPLE:

SELECT state, ROUND(100.0 * sum(CASE WHEN elevation >= 2000 THEN 1
ELSE 0 END) / count(*), 2 ) AS percentage_high_elevation_airports FROM airports WHERE state IS NOT NULL GROUP BY state;

Now you might think it would be cleaner to ROUND the AS var 'percentage_high_elevation_airports' but this gives an error.
EXAMPLE:

SELECT state, 100.0 * sum(CASE WHEN elevation >= 2000 THEN 1 ELSE 0 END) / count(*) AS ROUND(percentage_high_elevation_airports,2) FROM airports WHERE state IS NOT NULL GROUP BY state;


#9

Try my solution:

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;


#10