Use of "COUNT (column_name)" vs "COUNT(*)"


#1


https://www.codecademy.com/en/courses/sql-table-transformation/lessons/conditional-aggregates/exercises/combining-aggregates-ii?action=lesson_resume&link_content_target=interstitial_lesson


In this exercise, I am asked to find the percentage of high elevation airports (elevation >= 2000) by state from the airports table.

Q1: For the code below, why does the correct answer only appear when I use "COUNT(*)", not also "COUNT(elevation)"?

{I tested in the codeacademy console: COUNT(elevation) will bring the same number as "COUNT(*)".
(Trying "SELECT state, COUNT(*);" and "SELECT state, COUNT(elevation);" bring the same results).}

Q2: In fact, when I switch out "COUNT(*)" for "COUNT(elevation)" in the codeacademy answer, no data appear in the query results. Is this because codeacademy is forcing only 1 "correct" answer from among many possible answers?

Q3: I find that codeacademy answers often use "COUNT(*)" when I would think that "COUNT(column_name)" would be more appropriate.
Is there a reason for heavy use of "COUNT(*)" in the world of SQL?

Thank you very much for your help!



SELECT state, 100 * (SUM(CASE WHEN elevation >= 2000 THEN 1 ELSE 0 END)/COUNT(*))AS percentage_high_elevation_airports
FROM airports
GROUP BY 1;


Ind the percentage of high elevation airports (elevation >= 2000) by state from the airports table
#2

From the quick bit of research I did it looks as though COUNT(*) will count every row, whereas COUNT(column_name) will return every row that is not null.


#3

Thanks for the quick reply and for reminding me about this distinction! Would this mean that a NULL value is causing errors that prevents the code from running?


#4

I don't think so. What error is it giving you? I just ran it in my exercise and it worked fine with either COUNT(*) or COUNT(elevation).


#5

Thanks for checking it...after trying on another computer after half a day, it does work. I'm sorry for wasting your time, I'm not sure what was going on. On the first computer, it just wouldn't give any results for my query. It seems to work now.

Thank you again for your help!


#6

It was by no means a waste of time! I was glad to help.


#7

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