Count Function Use Cases

Authors:  Noga Levy – BI solution expert

Overview

The COUNT function in SQL, is a basic aggregate function that can be used in a variety of scenarios from basic to complex ones.
in addition, Count can also be used as a window aggregates function (click here to read more about window functions).

 The function returns the number of items found in a column in ‘int’ data type. It works on every data type except ‘text’, ‘ntext’, and ‘image’.

• COUNT ( <expression> ) – returns the number of items in the group (including duplicate values) excluding Nulls.
• COUNT ( ALL <expression> ) – similar to COUNT ( <expression> ).
• COUNT ( DISTINCT <expression> ) – returns the number of values in the group (exclude duplicate values) excludes Nulls.
• COUNT ( * ) – returns row number (include duplicate values) includes rows containing Null.

COUNT (*) does not support the use of DISTINCT. 

COUNT (1) – COUNT (1) is similar to COUNT (*) but works much faster.  A common misconception is assuming that the one refers to the first column.

Let’s take a look to check if COUNT (1) is similar to COUNT (*). We need a table with null in the first column. If the one refers to the first column, we accept the result will be 4 (the count of every value that is not null). But if the results will be 5 then we need to investigate what the one refers to.

 SELECT COUNT(*) as 'count(*)', COUNT (1) as 'count(1)', COUNT(name) as 'count name'
 FROM [Table]

Now we can see clearly that the COUNT (1) equals COUNT(*). Then what is the one referring to?

Let’s try another approach. if we try to enter a negative number into the count function, what do you think we will get?

 SELECT COUNT(-5) as 'count(-5)'
 FROM [Table]

   

Once again, the result is the same. So, what does the value represents?

The value we enter in the COUNT() function is the value that assigns to every row in the table. Then the function will count how many times the * or 1 or -5 repeats in the table and this number is equal to the number of rows in the table.

The one is not mandatory and can be replaced with any number or even string. The value we enter into the COUNT() function can be every number or even string.

 SELECT COUNT('results') as 'count(results)'
 FROM [Table]

Conclusion

The count function is an important aggregate function that has several different syntaxes which can be applied according to the scenario requirements.