Tuesday, April 5, 2022

DB - Count(*) vs count(1) vs count(column_name)

In general, count function accepts only one argument and counts the number of rows.

Example table:

Office with 5 employees:

Id name

1 ram

2 sham

3 jadu

4 madhu



1. Count(*): when * is used as an argument, it simply counts the total number of rows including the NULLs.

Query: Select count(*) as total from office

Output: Total: 5


2. Count(1): it counts all the rows including NULLs. What count(1) really does is that it replaces all the records we get from the query result with the value 1 and then counts the rows.

Query: Select 1 from office







Query: Select count(1) as total from office

Output: total: 5


3. Count(column_name): it counts all the rows but not NULLs.

Query: Select count(id) as total from office

Output: 5

Query: Select count(name) as total from office

Output: 4

No comments:

Post a Comment

Featured Post

11g to 12c OSB projects migration points

1. Export 11g OSB code and import in 12c Jdeveloper. Steps to import OSB project in Jdeveloper:   File⇾Import⇾Service Bus Resources⇾ Se...