Loading ...

Count() function,differences between Count(*),Count(columnname),Count(distinct columnname) | CodeAsp.Net

Use of Count Function in SQL

 /5
0 (0votes)

SQL Count() function returns number of Rows in a certain table. Count() is an aggregate function(aggregate function returns a single value). To understand the Count() function I have  explained that with  following example:

Create Table users
(
FirstName varchar(50),
City varchar(50)
)


Insert into users (FirstName,City) values(null,'Noida')
Insert into users (FirstName,City) values('vijendra','Delhi')
Insert into users (FirstName,City) values('singh','Mumbai')
Insert into users (FirstName,City) values(null,'CallCutta')
Insert into users (FirstName,City) values('shakya','Hyderabad')
Insert into users (FirstName,City) values('vijendra',null)

Syntax of Count function is:

Select Count(expression) From TableName

SQL COUNT(columanname) Syntax: The COUNT(columanname) function returns the number of values (NULL values will not be counted) of the given column:

Select Count(FirstName) as TotalUser from users

Results:

TotalUser
4

SQL COUNT(*) Syntax: The COUNT(*) function returns the number of values of the given column:

Select Count(*) as TotalUser from users

Results:

TotalUser
6

SQL COUNT(distinct columnname) Syntax: The COUNT(distinct columnname) function returns the number of values(Null values will not be counted and repeated values counted only once) of the given column:

Select Count(distinct FirstName) as TotalUser from users

Results:

TotalUser
3

Now you can also find the differences between Count(*),Count(columnname),Count(distinct columnname). i.e.

COUNT(*) counts all rows including Null values of the table.
COUNT(columanname) null values not count of the given table's column name.
COUNT (distinct columanname) Null values will not be counted and repeated values counted only once.

Comments (no comments yet)

Top Posts