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

SQL Server MIN

MIN functions the lowest of the data of a particular column . The data maybe integer , numeric decimal ,date, character type. MIN  return the lowest alphabetical value when applied on character set.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

Example 1
Here is example of MIN applied on a integer field. The example return the  the highest value of the   column applied.

SELECT MIN(age) FROM student

Result :12

Example 2



Here is the example of MIN applied on a character field. The highest alphabetical value will be  return.

SELECT MIN(name) FROM student

Result :John1




Min discard null. It does not work on null.It work on DISTINCT,All,group by, order by partition over etc.
 
Example 3
Here is the example of MIN applied with Distinct.  

SELECT  MIN(DISTINCT height) FROM student

Result : 10.50

Example 4

Here is the example of Min with group by.

SELECT age as age_group,MIN(height)

                    FROM student

                    GROUP BY age

Result :

age_group height

12         10.50
13         11.00
14         14.50
15         14.00
16         16.00

Example 4
Here is the example of partition over with MIN. Here MINcan be used over each partition.
 

SELECT DISTINCT age as age_group,MIN(height) OVER (PARTITION BY age) AS height

FROM student

Result :

age_group height

12        10.50
13        11.00
14        14.50
15        14.00
16        16.00

Example 5
Here is the example of MIN on a date field.
 

SELECT MIN(date_of_birth) FROM student

 

Result : 2018-01-08 00:00:00.000