COUNT(*) returns the number of items in a group, including NULL values and duplicates.COUNT(ALL expression) evaluates expression for each row in a group and returns the number of nonnull values. COUNT(DISTINCT expression) evaluates expression for each row in a group and returns the number of unique, nonnull values.
COUNT(*) : return total number of rows in a table
COUNT(ColumnName) : return total number of rows from a table WHERE ColumnName IS NOT NULL
COUNT(DISTINCT ColumnName): return total number of rows from table but ignores duplicates values and null values.
COUNT(1) : return total number of rows in a table
count(1) and count(*) are return the same results, and are equally efficient.
Example
CREATE TABLE Student(sid INT identity PRIMARY KEY
,Name Varchar(255))
INSERT INTO Student(Name)VALUES(‘abc’)
INSERT INTO Student(Name)VALUES(‘pqr’)
INSERT INTO Student(Name)VALUES(‘abc’)
INSERT INTO Student(Name)VALUES(NULL)
— Return 4, Number of rows in table
Select count(*) from Student
— Return 3, ignores NULL values
Select count(Name) from student
— Return 2, ignores NULL and duplicates values
Select count(distinct Name) from student
— Return 4, Number of rows in table
Select count(1) from student
