SQL Server COUNT

COUNT return the number of item found in the query. Count always return integer value to indicate the number of items.

We have created a table and corresponding data to do some sample example.

CREATE TABLE student

(

         id             INT NOT NULL,

         name           VARCHAR (500) NOT NULL,

         age            INT NOT NULL,

         adress         VARCHAR (500) NOT NULL,

         date_of_birth  DATETIME NOT NULL,

         height         DECIMAL(10,2) NOT NULL

)

INSERT INTO student(id,name,age,adress,date_of_birth,height)

SELECT 1,'John1',12,'34,student street','01/08/2018',10.5

UNION

SELECT 1,'John2',13,'35,student street','02/08/2018',11

UNION

SELECT 1,'John3',12,'36,student street','03/08/2018',11.5

UNION

SELECT 1,'John4',13,'37,student street','04/08/2018',12

UNION

SELECT 1,'John5',12,'38,student street','05/08/2018',12.5

UNION

SELECT 1,'John6',13,'39,student street','06/08/2018',13.5

UNION

SELECT 1,'John7',15,'40,student street','07/08/2018',14

UNION

SELECT 1,'John8',14,'41,student street','08/08/2018',14.5

UNION

SELECT 1,'John9',12,'42,student street','09/08/2018',15

UNION

SELECT 1,'John10',15,'43,student street','10/08/2018',15.5

UNION

SELECT 1,'John11',16,'44,student street','11/08/2018',16

Here is example of simple COUNT. 
Example 1

SELECT COUNT(date_of_birth) FROM student

Result :11

Count accept column number , * as parameter.With this parameter ,count return number of rows.Below is the example.

Example 2

SELECT COUNT(1) as Count1,

              COUNT(2) as Count2,

              COUNT(*) as Count3

                    FROM student

 

Result :

Count1  Count2  Count3
11           11           11

Count work with distinct , all , null ect .

Here is example of simple count statement with distinct value..It will return distinct number count.

Example 3

SELECT COUNT(DISTINCT age)

              FROM student

     
Result :12

Count statement  can be applied on order by partition by Group by also.

Here is the example of count statement with group by
Example 4

SELECT age as age_group,COUNT(height) AS number_of_count

from student

GROUP BY age

age_group  number_of_count

12          4
13          3
14          1
15          2
16          1

Here is the example of count statement with partition over.
Example 5

SELECT DISTINCT age as age_group,

COUNT(height) OVER (PARTITION BY age) AS height

FROM student

age_group  number_of_count

12                       4
13                       3
14                       1
15                       2
16                       1