Tags

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


SQL Puzzle | The Count Puzzle

In this puzzle you have to check for each Id we have to count a, b, c and d values. If for each Id these values are not present then do not include them. For more details please check the sample input and expected output.

Sample Input

Id Vals
1 a
1 b
1 c
1 d
2 a
2 d
3 d
4 g

Expected Output

Id cnts
1 4
2 2
3 1
4 0

Script

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

--

CREATE TABLE Table1
(
     Id VARCHAR(10) 
    ,Vals VARCHAR(10)
)
GO
 
INSERT INTO Table1 VALUES
(1, 'a'),
(1, 'b'),
(1, 'c'),
(1, 'd'),
(2, 'a'),
(2, 'd'),
(3, 'd')
GO
 
INSERT INTO Table1 VALUES (4,'g')
GO

SELECT * FROM Table1
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

SOLUTION 0 | USING SELECT & CASE

--

select Id,ISNULL(SUM(IIF(Vals IN ('a','b','c','d'),1,0)),0) CountVals 
FROM Table1
group by Id

--

Output-1

--                            

Id         CountVals
---------- -----------
1          4
2          2
3          1
4          0


(4 rows affected)

                                
--

SOLUTION 1 | USING Outer Apply AND LEFT JOIN

--

SELECT n.Id, COUNT(t1.Vals) CountVals FROM 
(
	SELECT * FROM (SELECT Id FROM Table1 GROUP BY Id) t1
	OUTER APPLY ( VALUES ('a'),('b'),('c'),('d')) AS y(t) 
)n
LEFT JOIN Table1 t1 ON t1.Vals = n.t AND t1.Id = n.Id
GROUP BY n.Id
GO


--

Output-1

--                            

Id         CountVals
---------- -----------
1          4
2          2
3          1
4          0


(4 rows affected)

                                
--

SOLUTION 2 | USING PIVOT AND CASE

--

;WITH CTE AS
(
    SELECT * , 'Yes' Ya FROM Table1
)
,CTE1 AS
(
	SELECT Id, [a],[b],[c],[d] 
	FROM CTE
	PIVOT
	(
		MAX(Ya) FOR Vals IN ([a],[b],[c],[d])
	)t
)
SELECT Id , 
		 CASE WHEN [a] = 'Yes' THEN 1 ELSE 0 END + 
		 CASE WHEN [b] = 'Yes' THEN 1 ELSE 0 END + 
		 CASE WHEN [c] = 'Yes' THEN 1 ELSE 0 END + 
		 CASE WHEN [d] = 'Yes' THEN 1 ELSE 0 END Cnt
FROM CTE1


--

Output-2

--                            

Id         CountVals
---------- -----------
1          4
2          2
3          1
4          0


(4 rows affected)



                               
--

SOLUTION 3 | USING MAX , CASE AND Group BY

--

SELECT Id , 
		 CASE WHEN [a] = 'Yes' THEN 1 ELSE 0 END + 
		 CASE WHEN [b] = 'Yes' THEN 1 ELSE 0 END + 
		 CASE WHEN [c] = 'Yes' THEN 1 ELSE 0 END + 
		 CASE WHEN [d] = 'Yes' THEN 1 ELSE 0 END Cnt
FROM 
(
	SELECT
		Id
		,MAX(CASE WHEN Vals = 'a' THEN 'Yes' ELSE 'No' END) [a] 
		,MAX(CASE WHEN Vals = 'b' THEN 'Yes' ELSE 'No' END) [b]
		,MAX(CASE WHEN Vals = 'c' THEN 'Yes' ELSE 'No' END) [c]
		,MAX(CASE WHEN Vals = 'd' THEN 'Yes' ELSE 'No' END) [d]
	FROM Table1
	GROUP BY Id
)k

--

Output-3

--                            

Id         CountVals
---------- -----------
1          4
2          2
3          1
4          0


(4 rows affected)





                                
--

SOLUTION 4 | 

--

SELECT t.Id,SUM(CASE WHEN CountVals IS NULL THEN 0 ELSE 1 END) cnts from Table1 t
LEFT JOIN
(
	SELECT n.Id, COUNT(n.Vals) CountVals FROM 
	Table1 n 
	WHERE vals IN ('a','b','c','d')
	GROUP BY n.Id
)k on t.Id = k.Id
GROUP BY t.Id
GO


--

Output-4

--                            

Id         CountVals
---------- -----------
1          4
2          2
3          1
4          0


(4 rows affected)





                                
--

SOLUTION 5 | Using CASE and SUM

--

SELECT
	Id
	,SUM(CASE WHEN Vals = 'a' THEN 1 ELSE 0 END)  
	+SUM(CASE WHEN Vals = 'b' THEN 1 ELSE 0 END)
	+SUM(CASE WHEN Vals = 'c' THEN 1 ELSE 0 END)
	+SUM(CASE WHEN Vals = 'd' THEN 1 ELSE 0 END) Cnt
FROM Table1
GROUP BY Id
GO


--

Output-5

--                            

Id         CountVals
---------- -----------
1          4
2          2
3          1
4          0


(4 rows affected)

                                
--

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

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com