Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


T-SQL Query | [ Count NULL Values Puzzle ]

The puzzle is very simple. Here you have to count the null values from a single column; there are multiple ways to achieve this, Please post all the possible solutions. Please check out the sample input and expected output for details.

Sample Input

ID
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
2
3
8
9
NULL

Expected output

NULLCount
9

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script

Use the below script to generate the source table and fill them up with the sample data.

CREATE TABLE CountNULL
(
 ID INT NULL
)
GO

INSERT INTO CountNULL(ID)
VALUES
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(2),
(3),
(8),
(9),
(NULL)

UPDATE – 10-Apr-2015 – Solution 1 & 2 


-- Solution 1 --

SELECT COUNT(1) NULLCount FROM CountNULL WHERE ID IS NULL


-- Solution 2 

SELECT SUM( CASE WHEN ID IS NULL THEN 1 ELSE 0 END ) NULLCount FROM CountNULL

--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com