SQL Count — What the count() function actually counts?

The count() function is widely used in SQL and sometimes without fully understanding the impact that the entry parameter to the function can alter the output of your results. Most commonly, the functions can have the following as inputs parameters:

1. Count(*)
2. Count(1)
3. Count(column name)

Test Scenario

Let’s create a table and insert a few records to try each count variation and compare the results.

create table count_scenario (a int);
insert into count_scenario values(1), (2), (4), (NULL), (NULL);

This will produce a total of 5 rows with 1, 2, 4 and 2 NULL values.

Count(*)

Count(1)

Count(a)

Conclusion:

As we can notice, using count(*) or count(1) it will not impact the final result since both will count the number of lines existing in the selection. On the other hand, count(a) will actually count all the lines excluding NULL’s. This needs to be taken in consideration when in a query the intent is to get the number of lines in a column. Depending on the possible values of that column the output may not be what you are expecting.