SQL COUNT

SQL Server COUNT() function is an aggreagate 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

Recommended for you

SQL AVG Function

SQL MAX Function

SQL MIN Function

SQL SUM Function







 82 total views,  1 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.