7. Combining aggregates II


#1

I have a question about this exercise:

Here's what the answer 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;

My question is about the count(). What is * in count() in this case? I know that * means all, but it means something different now?


#2

Hi, dynom
I think * still means everything in this context. Specifically, it means including everything (every variables/columns) in each row selected. However, the count function doesn't focus on how many variables in each row, and instead it returns how many rows selected (how many airports in each state in this case). Accordingly, a row with all variables included is counted as 1 row; a row with only one variable is counted as 1 row, too. So you can replace "count(*)" with "count(elevation)" or count any other variable in your answer, and I believe it will give you the same answer.

By the way, I figure out another answer. You can also use count function instead of sum function in numerator; just replace "sum" with "count" and replace "0" with "null"; then same answer will return.


#3

Okay, I think I got that explanation. Thank you.


#4

It should just be a placeholder to avoid an arg-array error, as long as you're using counting non-null columns, the result would be the same, it's just because there is no need to isolate a column. It's safer to just not narrow the count parameter.


#5

Great point. I was trying to use COUNT for the numerator, but set the ELSE to equal 0. Was trying to figure out what was wrong! :joy: