SQL COUNT() Function
SQL COUNT() function returns the number of records from a table in a SELECT statement.
SELECT COUNT (columnName) FROM TableName
SELECT COUNT (*) FROM TableName
Note: Count(*) will return all the records from a table whereas COUNT(columnName) will return the number of values for specified column excluding NULL values.
Here we are going to use Student table for demonstration purpose.
In the result pane you can see there are total 15 records in the table.
SELECT COUNT(*) FROM Student
Now, we will take the count of LastName column as follows.
SELECT COUNT(LastName) FROM Student
Count(LastName) has returned only 13 records which means that COUNT() function ignores NULL values in the specified column.
You can also use WHERE clause with COUNT() function to filter out the conditions as follows.
SELECT COUNT(LastName) FROM Student WHERE LastName = 'Miller'
We will take one more example for COUNT() function with DISTINCT clause.
SELECT COUNT(DISTINCT LastName) FROM Student
As you can see in the output there are only 11 distinct records in LastName column.
Reference: Manzoor Siddiqui [www.SQLServerLog.com]