SQL Puzzle | DISTINCT Count with NULL Puzzle – A single SELECT? | SQL Interview Question

In this puzzle you have to distinct records for each Id. The challege here is to do that in a single select. The other condition is that we need to consider NULL while considering distinct records. This is one of my Favorite interview question. Don’t know why it was not published earlier.

Please check the sample input and the expected output.

Can you do this in a single select ?

Sample Input

Expected Output

Use below script to create table and insert sample data into it.

--

CREATE TABLE GetUniqueCountwithNULLs
(
	 Id INT
	,Vals VARCHAR(100)
)
GO

INSERT INTO GetUniqueCountwithNULLs VALUES
(1,'a'),
(1,NULL),
(2,'b'),
(2,'b'),
(2,'b'),
(2,'c'),
(2,NULL),
(3,NULL),
(3,NULL),
(4,'a'),
(4,'a'),
(4,'a')
GO

SELECT * FROM GetUniqueCountwithNULLs
GO

--

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

4 SOLUTIONS

SOLUTION – 1 | A SINGLE SELECT Solution

--

SELECT Id , COUNT(DISTINCT Vals) + MAX(CASE WHEN Vals IS NULL THEN 1 ELSE 0 END)  DistinctCount
FROM GetUniqueCountwithNULLs
GROUP BY Id 

--

Output-1

--                            


Id          DistinctCount
----------- -------------
1           2
2           3
3           1
4           1


(4 rows affected)



--

SOLUTION – 2 | A SINGLE SELECT Solution – Solution BY Vikas G

--

SELECT Id , COUNT(DISTINCT IIF(Vals IS NULL,'a',Vals+' a')) DistinctCount
FROM GetUniqueCountwithNULLs
GROUP BY Id


--

Output-2

--                            


Id          DistinctCount
----------- -------------
1           2
2           3
3           1
4           1


(4 rows affected)



--

SOLUTION – 3 | TWO SELECT SOLUTION with ROW_Number()

--

;WITH CTE AS 
(
	SELECT *, ROW_NUMBER() OVER (PARTITION BY id,vals ORDER BY vals) AS RN FROM GetUniqueCountwithNULLs 
)
SELECT Id, COUNT(*) DistinctCount from cte 
WHERE RN=1 
GROUP BY ID 

--

Output-3

--                            


Id          DistinctCount
----------- -------------
1           2
2           3
3           1
4           1


(4 rows affected)



--

SOLUTION – 4 | TWO SELECT SOLUTION with Group By

--

SELECT ID, COUNT(DISTINCT Vals) , (COUNT(*) - COUNT(vals)) DistinctCount
FROM
(
	SELECT ID,Vals FROM GetUniqueCountwithNULLs 
	GROUP BY ID,VALS
)a 
GROUP By ID
GO 

--

Output-4

--                            


Id          DistinctCount
----------- -------------
1           2
2           3
3           1
4           1


(4 rows affected)



--

Related Puzzles

Add a comment if you have any other or better solution in mind. I would love to learn it. We all need to learn.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com