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

<PLEASE USE THIS TEMPLATE TO HELP YOU CREATE A GREAT POST!>

<Below this line, add a link to the EXACT exercise that you are stuck at.>
https://www.codecademy.com/en/courses/sql-table-transformation/lessons/conditional-aggregates/exercises/combining-aggregates-ii?action=lesson_resume&link_content_target=interstitial_lesson

<Below this line, in what way does your code behave incorrectly? Include ALL error messages.>
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!

<In this course, it often helps to include a screenshot of your whole web browser – that lets everyone see what you see. If you wish to include a screenshot, add it below this line.>

<If you wish to copy/paste in your code, you can use this next section. This will allow others to copy/paste your code for testing – something that they won’t be able to do with just a screenshot.>

```

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

<do not remove the three backticks above>

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.

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?

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).

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!

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

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