How Count() Function behaves with Different Parameters

SQL COUNT() Function

The COUNT() function returns the number of rows that matches a specified criteria. It is an aggregate function that counts the number of rows accessed in an expression

Count(*)- SELECT COUNT(*) FROM Table_Name;

• Returns total number of rows in a table regardless of NULL values and duplicates.
• Asterisx is a typical sign of a meaning “Everything” so this function counts ALL the rows. if you want to count all the rows in a table you should use COUNT(*). 

Count(Col)-  SELECT COUNT(Col) FROM Table_Name;

• Return count of non-null values in column Col.

Count(<Expression>)

• Returns not null occurrences. Number of records in the table regardless of NULL values and duplicates.
• If you want to count not null occurrences in a table – the best solution is COUNT(<expression>).
• COUNT(1) is nothing other than COUNT(<expression>).

Let's see how above Function works- 

 SQL> SELECT * FROM TTT;  
     ID NAME      SALARY STATUS   DEPARTMENT  
 ---------- ---------- ---------- ---------- --------------------  
     102 Ankit      8000 Active   Insurance  
     105 Rajan      18000 Active  
     101 Ravi      89000 Active   BFSI  
     106 Pritesh     11000 InActive  
     107 Karan     101000 Active  
     108 Subho      78000 Active   CISCO  
     109 Manu      777000 Active   Security  
     103 Tirath     18000 InActive  BFSI  
     112 Pankaj     90000 Active  
     113 Abhishek    44000 Active   Retail  
     114 Sekhar      4000 Active   Security  
 11 rows selected.  

 SQL> SELECT Count(*),Count(1), Count(Department), Count('Hello') From TTT;  
  COUNT(*)  COUNT(1) COUNT(DEPARTMENT) COUNT('HELLO')  
 ---------- ---------- ----------------- --------------  
     11        11           7                11  

Note- Occasionally COUNT(*) would have a higher CPU time and sometimes COUNT(1) would have a higher CPU time. But neither was drastically different from the other. In addition to the statistics from the run if you look at the execution plans for both of these two they will be the exact same.

"1" is a non-null expression: so it's the same as COUNT(*). The optimizer recognizes it for what it is: trivial.

The same as EXISTS (SELECT * ...                or              EXISTS (SELECT 1 ...

Conclusion: COUNT(*) and COUNT(1) are the same. No matter what you do, you end up doing COUNT(*)  

Let’s Count NULLs and Not NULLs values in the Table Data below (Count total records where Department is Null and Not Null)

 SQL> SELECT * FROM TTT;  
     ID NAME      SALARY STATUS   DEPARTMENT  
 ---------- ---------- ---------- ---------- --------------------  
     102 Ankit      8000 Active   Insurance  
     105 Rajan      18000 Active  
     101 Ravi      89000 Active   BFSI  
     106 Pritesh     11000 InActive  
     107 Karan     101000 Active  
     108 Subho      78000 Active   CISCO  
     109 Manu      777000 Active   Security  
     103 Tirath     18000 InActive  BFSI  
     112 Pankaj     90000 Active  
     113 Abhishek    44000 Active   Retail  
     114 Sekhar      4000 Active   Security  
 11 rows selected.  

 SQL> SELECT  
  2 Count(Department) DEPT_NOT_NULLs,  
  3 Count(*)-Count(Department) DEPT_NULLs,  
  4 Count(*) TotalRecords  
  5 From TTT;  
 DEPT_NOT_NULLS DEPT_NULLS TOTALRECORDS  
 -------------- ---------- ------------  
        7     4      11  

OR we can also use below SQL for the same.

 SQL> SELECT  
  2 Count(Department) DEPT_NOT_NULLs,  
  3 SUM(Case When Department IS NULL Then 1 Else 0 End) DEPT_NULLs,  
  4 Count(*) TotalRecords  
  5 From TTT;  
 DEPT_NOT_NULLS DEPT_NULLS TOTALRECORDS  
 -------------- ---------- ------------  
        7     4      11  

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit. So, leave your thoughts before you leave the page.