COUNT Function in SQL

   Part - 20

--********************************************

-- 20 - Count Function in SQL Server - While Loop 

--********************************************

Click here for you tube video answer:

DROP TABLE IF EXISTS tblEmployee

GO

CREATE TABLE tblEmployee (Empid INT NOT NULL

,Empname VARCHAR (20)

,Salary INT NOT NULL

,Dept INT NULL) 

INSERT tblEmployee VALUES 

(1, 'Smith', 10600, 3), (2, 'George',8000 , 3), (3, 'Peter', 10500, 5)

, (4, 'Allen', 11000, 5), (5, 'Peter', 10000, NULL)

--> 1. COUNT(*) ----------------------------------

--> 2. COUNT(Col_1)-------------------------------

--- When Column is NOT NULLABLE

SELECT * FROM tblEmployee 

SELECT COUNT(Empid) FROM tblEmployee 

--- When Column is NULLABLE

SELECT COUNT(Dept) FROM tblEmployee

--> 3. COUNT(DISTINCT Col_1)-------------------------------

Click on Messages and you will see the below warning that the NULLS are being ignored. Please refer to my Previous video Part 19 of this series to know more about the Warning details

--> 4. COUNT_BIG(*)----------------------------------------

CREATE TABLE BigINT_Table (ID BIGINT NOT NULL)

DECLARE @ID BIGINT

SET @ID = 1

WHILE (1=1)

BEGIN

INSERT BigINT_Table VALUES (@ID)

SET @ID = @ID + 1

 

IF @ID > 2147483647

  BREAK; 

END

Since the table records of the table have crossed the integer range, it has given the above error

to avoid that we need to use COUNT_BIG function