SQL Server COUNT() function is an aggregate function that returns the number of rows in a table. It sets the number of rows or non NULL column values.
SYNTAX
COUNT(*) COUNT(DISTINCT] expression )
* asterisk counts all the rows in the table whether or not they include nulls.
Distinct returns the number of unique, non-null values. It can not be used with asterisk *.
Let look at an example of COUNT in SQL Server.
First we create a table and insert some records into table as shown below.
CREATE TABLE TBL (VALUE INT) INSERT INTO TBL VALUES(5),(5),(null),(null),(7),(8),(9),(9),(10)
SELECT * FROM TBL
To get all number of rows counts
Following statement uses count function and returns all rows counts including nulls.
SELECT COUNT(*) AS TOTAL_CNT FROM TBL
To get the number ofย non null rows counts
Following SQL statement returns the number of rows counts only for non null values for a specific column.
SELECT COUNT(VALUE) AS TOTAL_CNT FROM TBL
ย To get the number of unique non-null values
Following SQL statement uses count function and returns the number of unique non null values .
SELECT COUNT(DISTINCT VALUE) AS TOTAL_CNT FROM TBL
Also Read..