Difference Between COUNT(*), COUNT(1), COUNT(column name), and COUNT(DISTINCT column name)

If you have been writing SQL queries for a while, you must be aware of the COUNT() function. It’s very simple to use, but it has multiple variations which often confuses people about when and exactly how to use them. In this tutorial, we are going to learn about different variations of COUNT functions and their uses with multiple examples.

Introduction to COUNT() in SQL

SQL COUNT() function counts the number of rows that meet the criteria stated in the parenthesis. It displays the number of rows that match your criteria rather than returning the rows themselves.

Syntax:

SELECT COUNT(column_name)
FROM table_name;Code language: SQL (Structured Query Language) (sql)

Example:

Suppose you have a record of the passengers travelling on a certain train and you have to count the number of passengers, then it is very difficult to do it manually. But it can be easily done through the SQL COUNT() function. Now, consider the Passenger Table shown below.

We will use this for our examples.

Also Read: Using COUNT() with JOIN in SQL: A Useful Combination

COUNT() Variants in SQL

Let’s now see the difference between COUNT(*), COUNT(1), COUNT(column name), and COUNT(DISTINCT column name).

COUNT(*)

COUNT(*) returns the count of the total number of rows in a table regardless of whether the columns contain the NULL value or not. It counts all the rows, including those with null values.

Syntax:

SELECT COUNT(*) FROM table_name;Code language: SQL (Structured Query Language) (sql)

Example:

If we want to count the number of rows in the whole table, we will use the following query:

SELECT COUNT(*) AS total_rows
FROM PASSENGER;Code language: SQL (Structured Query Language) (sql)

COUNT(1)

COUNT(1) is the same as COUNT(*). It also returns the count of the total number of rows in the table, regardless of the values in the column. It counts all the rows, including those with null values. The result set will be the same as COUNT(*).

Syntax:

SELECT COUNT(1) FROM table_name;Code language: SQL (Structured Query Language) (sql)

Example:

If we want to count the number of rows in the whole table, we will use the following query:

SELECT COUNT(1) AS count_rows
FROM PASSENGER;Code language: SQL (Structured Query Language) (sql)

Is it possible to change the number that the COUNT() function assigns? Yes, as can be seen below, the result will always return the whole number of rows. Because COUNT() counts the number of rows and replaces the query result set with a provided value, it produces the same output.

Although COUNT() and COUNT(1) may differ conceptually and historically, they both often produce the same result, which is the total number of rows that a query returns. Choosing between them typically comes down to preference or coding convention.

COUNT(column name)

COUNT(column name) counts the number of rows where the specified column is not NULL. It counts the occurrences of non-NULL values in the specified column.

Syntax:

SELECT COUNT(column_name) FROM table_name;Code language: SQL (Structured Query Language) (sql)

Example:

If we want to fetch the number of passengers in the Passenger table who have middle names, we will use the following query:

SELECT COUNT(middle_name) AS count_middle_name
FROM PASSENGER;Code language: SQL (Structured Query Language) (sql)

As shown above, the COUNT function returns ‘4’ because it has eliminated the NULL value present in the middle_name column.

COUNT(DISTINCT column name)

COUNT(DISTINCT column name) returns the count of distinct (unique) NON NULL values in the specified column. It does not count NULL values, and it only counts each unique value once.

Syntax:

SELECT COUNT(DISTINCT column_name) FROM table_name;Code language: SQL (Structured Query Language) (sql)

Example:

If we want to fetch the number of passengers in the Passenger table who have distinct ages, we will use the following query:

SELECT COUNT(DISTINCT age) AS count_age
FROM PASSENGER;Code language: SQL (Structured Query Language) (sql)

As shown above, the query with the DISTINCT() function returns the output as ‘4′. After all, it considers only a unique record from the age column.

Conclusion

In this tutorial, we have learned about multiple variations of the COUNT function and their differences. Also, we have seen how to use COUNT(*), COUNT(1), COUNT(column name) and COUNT(DISTINCT column name). Every variation has a specific purpose and may be applied based on the requirements.

Reference

https://stackoverflow.com/questions/66710755/what-happens-when-you-use-distinct-in-count-in-sql