Complex SQL Challenges, Complex TSQL Challenge, Interview Qs.SQL SERVER Questions, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Learn complex SQL, Learn SQL, Learn T-SQL, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL pl/sql puzzles, SQL Puzzles, SQL Queries, SQL SERVER Interview questions, SQL Skills, SQL Sudoku, SQLSERVER, T SQL Puzzles, T-SQL Challenge, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries
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.
- The solution should be should use “SELECT” statement or “CTE”.
- Add your solution(s) in the comments section or send you solution(s) to email@example.com
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.
select Cast (Count(ISNULL(ID,-1)) as Varchar(10)) NULLCount from CountNULL
where ID is NULL
group by ID
LikeLiked by 1 person
sanjay singh said:
select count(*)-COUNT(id) as countnull from #CountNULL