Tuesday, August 16, 2011

Difference between count(*) and count(columnname)

Posted by Rahul Kharde at 12:09 AM

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



If you enjoyed this post and wish to be informed whenever a new post is published, then make sure you subscribe to my regular Email Updates. Subscribe Now!


Kindly Bookmark and Share it:

YOUR ADSENSE CODE GOES HERE

0 comments:

Have any question? Feel Free To Post Below:

 

Popular Posts

Recent Comments

© 2011. All Rights Reserved | Help to understand .Net | Template by Blogger Widgets

Home | About | Top