SQL COUNT() Function

Submitted by admin on Friday, April 22, 2011 - 11:11.

COUNT is a simple aggregate function provided by SQL. As it is clear from its name that it simply counts the number of records in the table and returns the total count.

There are three (3) different usage for the Count function.

SQL COUNT(column_name)
COUNT(*)
COUNT(DISTINCT column_name)

Consider the following table for this exercise

Employess

Firstname	Lastname	Salary	DeptID
John	Smith	1000	1
Mathew	Simon	3000	1
Bill	Steve	2200	1
Amanda	Rogers		2
Steve	Hills	2800	2
Steve	jobs	2400	2
bill	cosby	700	3

SQL COUNT(column_name) Syntax

SELECT COUNT (Salary) FROM TABLE

This syntax will count the number of values in a row excluding the NULL value.

Example

SELECT COUNT(Firstname) AS Employee_with_Salary FROM Employees

Result of the Query

Employee_with_Salary
6

SQL COUNT(*) Syntax

SELECT COUNT(*) FROM TABLE_NAME

This syntax will count all records in your table.

Example

SELECT COUNT(*) AS Total_Employee FROM Employees

Result of the Query

Total_Employee
7

SQL COUNT(DISTINCT column_name) Syntax

SELECT COUNT(DISTINCT column_name) FROM TABLE_NAME

This syntax works the same as the SQL DISTINCT Clause that we have discussed in our previous chapter, except that it will return a distinct record on specific field.

Example

SELECT COUNT(Firstname) AS Unique_Employee_Name FROM Employees

Result of the Query

Unique_Employee_Name
6

As you can see on the above sample, you can also give an Alias to the Count function.

The Counter function will simply increments its value up to the last record in your table.

SQL COUNT() Function

Example

Example

SQL COUNT(DISTINCT column_name) Syntax

Add new comment