SQL COUNT

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..

SQL AVG Function

SQL MAX Function

SQL MIN Function

SQL SUM Function