SQL Tutorial – COUNT() – counting values in a column.

You want to count the number of Non-null values in a column. For example, you want to know for how many countries we have the LifeExpectancy data in this table.

Solution –

If you want to count the number of Non-Null values in a column, You have to use the COUNT() function and pass the column name to it.

SELECT
    COUNT(LifeExpectancy) as total_lifeExp_data
FROM
    country

So, here we have life expectancy data for 222 countries. In our previous post, we also looked at the COUNT() function but we passed the * character to the count function. When you count star in the COUNT(*) function , what you really count is the number of rows in that table or column which is why COUNT(*) will also include the Non-Null values as well as NULL values.

SELECT
    COUNT(*) as total_lifeExp_data
FROM
    country

Now, if you look at the result above, you can see that total number has increased from 222 to 239 because when you use COUNT(*), it is also counting the NULL values along with the Non-Null values. But for our problem, we want to know, for how many countries we have the Life Expectancy data, so we want to to use count function with the column name COUNT(LifeExpectancy) , using this will ignore the Null values in that column when doing counting.

Related Post – COUNT() – Counting rows in a table.

If you like this post then please share it with others and subscribe to our blog to learn data science.